Skip to main content
Reformatted my SQL for readability.
Source Link
Jeffery Thomas
  • 2.1k
  • 1
  • 14
  • 19

To be perfectly blunt, this sounds like a bad idea. This model mixes up some things which should stay separate.

From this spec, I see three data tables list, node, datum.

list

  • id
  • name

node

  • id
  • position_in_list
  • list_id
  • datum_id

datum

  • id
  • data_value_1
  • data_value_2

OK, so obviously you won't have a datum table, but it's a placeholder for your real data.

A list name 'foo' in the can then be reconstructed with:

SELECT nodedatum.*
  FROM datum
  LEFT JOIN node ON datum.id = node.datum_id
  LEFT JOIN list ON node.list_id = list.id  
WHERE list.name = 'foo'
  BY node.position_in_list

To be perfectly blunt, this sounds like a bad idea. This model mixes up some things which should stay separate.

From this spec, I see three data tables list, node, datum.

list

  • id
  • name

node

  • id
  • position_in_list
  • list_id
  • datum_id

datum

  • id
  • data_value_1
  • data_value_2

OK, so obviously you won't have a datum table, but it's a placeholder for your real data.

A list name 'foo' in the can then be reconstructed with:

SELECT node.* FROM node LEFT JOIN list ON node.list_id = list.id WHERE list.name = 'foo' BY node.position_in_list

To be perfectly blunt, this sounds like a bad idea. This model mixes up some things which should stay separate.

From this spec, I see three data tables list, node, datum.

list

  • id
  • name

node

  • id
  • position_in_list
  • list_id
  • datum_id

datum

  • id
  • data_value_1
  • data_value_2

OK, so obviously you won't have a datum table, but it's a placeholder for your real data.

A list name 'foo' in the can then be reconstructed with:

SELECT datum.*
  FROM datum
  LEFT JOIN node ON datum.id = node.datum_id
  LEFT JOIN list ON node.list_id = list.id 
WHERE list.name = 'foo'
  BY node.position_in_list
Source Link
Jeffery Thomas
  • 2.1k
  • 1
  • 14
  • 19

To be perfectly blunt, this sounds like a bad idea. This model mixes up some things which should stay separate.

From this spec, I see three data tables list, node, datum.

list

  • id
  • name

node

  • id
  • position_in_list
  • list_id
  • datum_id

datum

  • id
  • data_value_1
  • data_value_2

OK, so obviously you won't have a datum table, but it's a placeholder for your real data.

A list name 'foo' in the can then be reconstructed with:

SELECT node.* FROM node LEFT JOIN list ON node.list_id = list.id WHERE list.name = 'foo' BY node.position_in_list