2

I have an entity type called 'Object', and I need to model an object set as a list of unique objects with variable length. Then, each object could belongs to many object sets.

Current entity-relationship diagram (ERD)

This is my ERD so far, with a many-to-many relationship:

enter image description here

Tables design

At this point, we have the next tables:

Object table:

  • ObjectID (PrimaryKey)
  • ObjectAttribute1
  • ObjectAttribute2
  • [...]

ObjectSet table:

  • ObjectSetID (PrimaryKey)
  • ObjectSetAttribute1
  • [...]

ObjectBelongsObjectSet:

  • ObjectSetID (ForeignKey)
  • ObjectID (ForeignKey)

Using this model, we could have two different sets of objects with exactly the same combination of objects, which could never happen.

My question

How could improve my model to implement this restriction?

  • In that case the 'set of Objects' is an unique attribute of the ObjectSet, not a relation. – Akina Apr 23 at 11:43
  • as a multievaluated attribute of foreign keys? @Akina – Kroka Apr 23 at 11:44
  • But implementation... I do not remember the implementation which have both proper FK system and non-redundant data. – Akina Apr 23 at 11:49
  • 1
    A relational table? Is it like a parents table relating to children table? If yes it's simple, create a relational table with the FK to both tables. The constraint you are looking for is a bit more complicated and I don't believe there's something in the relational model can achieve it. But it can be easily done if you create some kind of computed hash field and put a unique index on it – jean Apr 23 at 11:52
0

The problem can not be solved with the relational model, but, as @jean says in the comments, we can calculate a hash of the objects that make up the set of objects and form a field with restriction of uniqueness.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.