0

I am learning about Database design, I have doubt about some database design conepts

I want to know which is better design between two design

Design 1

there is two tables: Object and Field

Object

| ID | NAME|

ID is PK

Field

| ID | OBJECT_ID | NAME

Both ID and OBJECT_ID are PK and OBJECT_ID IS FK


Design 2

there is three tables: Object, ObjectField and Field

Object

| ID | NAME |

ID is primary key

Field

| ID | NAME |

ID is primary key

ObjectField

| OBJECT_ID | FIELD_ID |

Both OBJECT_ID AND FIELD_ID is PK and FK

relation is stored in another table

  • 2
    This design pattern is called "Entity Attribute Value" - which is usually something you should try to avoid. In Postgres I would use a JSONB column to store multiple (dynamic) attributes in a single row. – a_horse_with_no_name Jan 19 at 9:01
  • Are you using "doubt" to mean "question"? – Walter Mitty Jan 19 at 11:45
  • What is this design for? – Walter Mitty Jan 19 at 11:47
  • This design is good only if you want to achieve low performance and want to make your queries complicated. – Laurenz Albe Jan 20 at 7:40
  • What you should do is create a single table for the object, and the table columns are the fields of the object. – Laurenz Albe Jan 20 at 7:43
2

There is no "better" between those two designs. They are just different. So it depends on what you want to store in your database. If every field you store belongs to just one object, then the first design is the better choice, but if you want to have fields and objects stored and each one can relate to another, the second design is better. Let me give you two short examples.

If object is a city with name and zip code like this:

ID          NAME    
90201       Bell
90202       Bell Gardens

And object are persons and where they live:

ID  OBJECT_ID   Name
1   90201       Name_Person_1
2   90201       Name_Person_2
3   90202       Name_Person_3

You could store where each person lives without the redundant information of the name in the second table. But each row in the Field-table has to match exactly one entry in the Object-table.

The second design is to be used, if there is a relation between Object and Field and one field can belong to many Objects and one Object may belong to many Fields. That can be the case for a relation used for classes. A student can be enrolled in many classes and in each class there are many students. So your Object table would be students and you Field table would be class. Then you would store the relationship between those two in the ObjectField table.

Students:
ID          NAME    
123         Joe
124         Jill

Classes:
ID          NAME    
1           Math
2           History

StudentClass:
Student_ID  Class_ID
1           1
2           1
1           2

The student-class table would store redundant information in the field-table in your first design. But on the other hand would the second design need more space due to a third table, which is unnecessary in the zip code example. So it really depends.

One last thing. In your first design it is enough, if only ID is a primary key in the field table. There is no need to have a combined primary key consisting of ID and OBJECT_ID.

  • 1
    There is no "better" between those two designs. Truly spoken, they are both bad. – Laurenz Albe Jan 20 at 7:42

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.