0

I am learning about database design. I have a question about some database design concepts.

I want to know which is the better of these two designs:

Design 1

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

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 are PK and FK.

Relation is stored in another table.

0

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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