CODEWITHSUNDEEP
c#sql-serverdatabasesql-server-2008database-design
Hoorayo
Hoorayo

Reputation: 615

Data modeling for Same tables with same columns

I have many tables that have same number of columns and names because they are all lookup tables. For example, there are LabelType and TaskType tables. LabelType and TaskType tables have TypeID and TypeName columns. They will be used as a foreign key in other tables such as LabelType table with shippingLog table and TaskType table with EmployeeTask Table.

LabelType Table
TypeID TypeName
1      Fedex
2      UPS
3      USPS

TaskType Table
TypeID TypeName
1      Receiving
2      Pickup
3      Shipping

So far, I have more than 20 tables and I am expecting it is going to be keep increasing. I have no problem with it , but I am just wondering whether there is any better or smarter way of using tables or not. I was even thinking to consolidate all those tables as one lookup Type Table and differentiate them by adding a foreign key from lookup table. The lookup table may have data like Label, Task, and etc. Then I just need one or two tables for all those lookup data.

Please, advise me if you have any better or smarter way of data modeling.

Upvotes: 2

Views: 857

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Just because data has similar structure doesn't mean it has the same meaning or same constraints. Keep your lookup tables separate. This keeps foreign keys separate, so the database can protect itself from referencing the wrong kind of lookup data.1

I wish relational DBMSes supported inheritance, where you could define the basic structure in the parent table and just add specific FKs in the child tables. As it stands now, you'll need to endure some repetition in your DDL...

NOTE: One exception from "keep lookup tables separate" rule might be when your system needs to be dynamic (i.e. be able to add new kinds of lookup data without actually creating new physical tables in the database), but it doesn't look that way from your question.

1 With one big lookup table, FKs alone won't stop (for example) the ShippingLog table from referencing a row meant for the EmployeeTask table. By using identifying relationships and migrating PKs, you can protect yourself from this, but not without introducing some redundancies and needing some careful constraining. It's cleaner and probably more performant to simply do the right thing and keep lookup tables separate.

Upvotes: 7

Walter Mitty
Walter Mitty

Reputation: 18940

Keep your lookup tables separate. It's faster at lookup time, and you will do millions of lookups between times when you add a new lookup table.

A lot of tables is not a big problem.

Upvotes: 1

Related Questions