0

I am the only DBA at our company and am relatively new in the profession (having 2.5 years of experience now). The developers and I are currently having a discussion about how to properly design the database for a new piece of software being developed. They develop their applications in modules and believe that having a private status table for each module is best, where as I believe having one table to hold all internally generated states (and then possibly additional tables to give those states some meaning) would be best. The sample code below is written in postgresql, since that's what we use.

My approach:

CREATE TABLE status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);

CREATE TABLE member_status (
  status_id INT PRIMARY KEY REFERENCES status (status_id)
);

Their approach:

CREATE TABLE member_status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);
-- rinse and repeat for every object that may need a status

They only see "problems" with my approach (which have yet to be clearly articulated) and, while I don't see problems with their approach, I do believe it is not the best design.

Any input into which design is better and WHY would be appreciated.

  • I've updated my answer, and I think I hit on the nub of the problem. The various possible states for each separate module is a distinct set of data. It seems counter-intuituve to merge it into a single set (table) because they share certain similarities. NOTE: They are similar - not the same: Perhaps they all have codes and descriptions, and some have extra columns. But you can't even be sure that all the codes and descriptions will have the same lengths. A member_status_code is similar, but not the same as an order_status_code. They shouldn't be in the same column. – Disillusioned Jul 9 '14 at 17:33
  • PS: I do also think it would be wrong to "rinse and repeat" a status table for every module. In some cases the options might be better served by application side rules and a simple constraint limiting the valid states in the main table of a module. – Disillusioned Jul 9 '14 at 17:38
0

The main problem with your approach occurs when you want to maintain referrential integrity to the status table.

Suppose you have a member table:

CREATE TABLE member (
  member_id SERIAL PRIMARY KEY,
  status_id int NOT NULL,
  ...
);

Now if you want to enfore referrential integrity to ensure each member has a valid status, you can't. This is because only a subset of the status rows applies to members. As a result, a member can be assigned a purchase_order_status and RI will still pass.

EDIT2

In a comment you clarified that in your solution member would have RI to the single-column member_status table. This suggests your design could land up having a large number of single column tables, just to control refferential integrity. Now any time you add a new status, you need to add it to 2 tables.

There's another problem with your master_status table.

status_code is unique. Meaning that purchase_order and sales_order cannot use the same codes. This seems to be an unnecessarily harsh restriction. However, droping the unique constraint risks duplicating codes within a category.

So the next thing you might consider is adding a status_type and enforcing the unique constraint accross both type and code. So your status_type attempts to fix the underlying problem by artifically splitting the master table into multiple tables.

Basically the problem boils down to: You are trying to consolidate multiple distinct sets of data into a single table because there is a similarity in that some of their columns overlap.


Joe Celko has written a fairly in-depth article on Lookup Tables in SQL.


EDIT On second thoughts, perhaps your approach is not that much like "One True Lookup Table" because you do acknowledge the need for additional tables where more detail is required. The RI issue still remains however.

  • 1
    Just in case it wasn't clear, the member table would have a foreign key to the member_status table, not the status table.. (I read what you said as that you are assuming it would have a fkey to the status table?) I have looked at the "one true lookup table" post you mentioned (before writing my question, even) - I believe his main issue is that in the OTLT people are trying to store DIFFERENT domains in the same table. I'm not sure my proposed design is quite the same, seeing as how a status is a status is a status (vs storing "status", "typeA", "typeB", etc). – Joishi Bodio Jul 9 '14 at 16:59
  • @JoishiBodio I added my edit before reading your comment. As for member table having foreign key to member_status in your design... Doesn't this mean that every module will land up with a minimum of its own status table with only one column for RI purposes? Maintaining that table feels like "work for work's sake:. I have noticed another problem, which I'll add to my answer. – Disillusioned Jul 9 '14 at 17:11
  • Yes, theoretically every module would still end up having its own status table. The actual number of tables that would exist would be x for their design and x+1for my design. But, on the flip side, all the separate states would be grouped into one central location and there would be little data duplication (although I'm not sure if one design vs the other would be more normalized or not .. perhaps my design falls under a normal form higher than 3.5 - I'll have to research that) – Joishi Bodio Jul 9 '14 at 17:16
  • I guess nobody else is going to chip in ... I appreciate your input, Craig. Thank you. :) I will leave this "open" for a few more days with hopes that others might comment. – Joishi Bodio Jul 11 '14 at 16:52

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.