mko mko -4 years ago 50
SQL Question

Architecture of SQL tables

I am wondering is it more useful and practical (size of DB) to create multiple tables in sql with two columns (one column containing foreign key and one column containing random data) or merge it and create one table containing multiple columns. I am asking this because in my scenario one product holding primary key could have sufficient/applicable data for only one column while other columns would be empty.

example a. one table
productID productname weight no_of_pages
1 book 130 500
2 watch 50 null
3 ring null null

example b. three tables
productID productname
1 book
2 watch
3 ring

productID weight
1 130
2 50

productID no_of_pages
1 500

Answer Source

The multi-table approach is more "normal" (in database terms) because it avoids columns that commonly store NULLs. It's also something of a pain in programming terms because you have to JOIN a bunch of tables to get your original entity back.

I suggest adopting a middle way. Weight seems to be a property of most products, if not all (indeed, a ring has a weight even if small and you'll probably want to know it for shipping purposes), so I'd leave that in the Products table. But number of pages applies only to a book, as do a slew of other unmentioned properties (author, ISBN, etc). In this example, I'd use a Products table and a Books table. The books table would extend the Products table in a fashion similar to class inheritance in object oriented program.

All book-specific properties go into the Books table, and you join only Products and Books to get a complete description of a book.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download