CODEWITHSUNDEEP
mysqlsqldatabasepostgresqldatabase-design
R. Kohlisch
R. Kohlisch

Reputation: 2993

Relational Database: Items whose Properties depend on Context

I am trying to set up a database, but I am somewhat lost as to how to structure it. It seems to make sense to make it relational, since in my Database Objects hold lists of atoms, and those atoms can reoccur in other Object items. So we have

Object Pseudocode:

{
  name: 'object1',
  atoms: [ 
    {name: 'atom1', color: 'red'}, 
    {name: 'atom2', color: 'green'}, 
  ...
  ]
}

What I struggle with is this: Atoms have different colors, depending on what object they appear in. So atom1 might be red in object1, but green in object2. Their color depends on the overall context and what other atoms are present in the object. But they are not conceptually distinct atoms because of that -it still makes sense to say atom1 is sometimes green, sometimes red. I still want to be able to count them as such.

I don't understand how I best model this onto a relational database. Atoms reoccur in Objects, but some of their properties might be different. So I can't simply say to Object1 to just look up atom1, atom2, & atom3 from the atoms TABLE. Any ideas?

Upvotes: 1

Views: 59

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Bit verbose, but shows the underlying design methodology: predicates and constraints in natural language.

All attributes (columns) NOT NULL

[p x]   = predicate  x
(c x.y) = constraint x.y

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key

[p 1] Object identified by OBJECT_ID, named OBJECT_NAME exists.

(c 1.1) Object is identified by OBJECT_ID.

(c 1.2) Each object has exactly one name; for each name, exactly one object has that name.

object {OBJECT_ID, OBJECT_NAME} -- p 1
    PK {OBJECT_ID}              -- c 1.1
    AK {OBJECT_NAME}            -- c 1.2

[p 2] Atom identified by ATOM_NAME exists.

(c 2.1) Atom is identified by atom name.

atom {ATOM_NAME}  -- p 2
  PK {ATOM_NAME}  -- c 2.1

[p 3] Atom color ATOM_COLOR exists.

(c 3.1) Atom color is identified by ATOM_COLOR.

atom_color {ATOM_COLOR}  -- p 3
        PK {ATOM_COLOR}  -- c 3.1

[p 4] Atom ATOM_NAME appears in object OBJECT_ID in color ATOM_COLOR.

(c 4.1) For each atom appearing in an object; that atom appearing in that object appears in exactly one atom color.

(c 4.2) For each atom appearing in a color; that atom may appear in that color in more than one object.

(c 4.3) For each object and atom color; more than one atom may appear in that object in that color.

(c 4.4) If an atom appears in an object in a color, then that atom must exist.

(c 4.5) If an atom appears in an object in a color, then that object must exist.

(c 4.6) If an atom appears in an object in a color, then that atom color must exist.

atom_object {ATOM_NAME, OBJECT_ID, ATOM_COLOR}  -- p 4
         PK {ATOM_NAME, OBJECT_ID}              -- c 4.1, 4.2, 4.3

FK1 {ATOM_NAME}  REFERENCES atom       {ATOM_NAME}  -- c 4.4
FK2 {OBJECT_ID}  REFERENCES object     {OBJECT_ID}  -- c 4.5
FK3 {ATOM_COLOR} REFERENCES atom_color {ATOM_COLOR} -- c 4.6

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This sounds like three tables:

objects

  • objectId
  • objectName

atoms

  • atomId
  • atomName

objectAtoms

  • objectId
  • atomId
  • color

I'm not sure if "color" should be a separate reference table as well.

Upvotes: 1

Related Questions