CODEWITHSUNDEEP
mysqldatabase-design
PinkFloyd
PinkFloyd

Reputation: 118

Database design slightly different object

What is the best way to design a db with object types that share some columns but also have some of there own ?

Have 1 table for the shared data and let each type has its own table

[objects] o_id | o_name | object_type

[object_type_1] o_t_id | o_id | option1 | option 2

[object_type_2] o_t_id | o_id | option3 | option 4

OR have all the columns in 1 table and have a lot of NULL's

[objects] o_id | o_name | object_type | option1 | option 2 | option3 | option 4

Upvotes: 3

Views: 78

Answers (2)

Nonym
Nonym

Reputation: 6299

It may depend on the purpose of the tables. Are you creating fact tables? Interaction tables? varied lookup tables?

Here's a link to a simple tutorial on database normalization:

http://www.phlonx.com/resources/nf3/

Could you tell us more about the table structures? And what is the forecast on the growth of these tables?

There's a chance, depending on your purpose, that approach 1 will be of use and in another, maybe approach 2 will be of use..

Upvotes: 0

cherouvim
cherouvim

Reputation: 31903

This is a common problem. A single best solution does not exist. You'll have to weigh the pros and cons of each always depending on your model.

Have a look at your options:

A different approach, for your extra properties, is EAV. This has some serious benefits but also some tremendous drawbacks, so becareful before thinking that it is the "silver bullet":

Upvotes: 1

Related Questions