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.
member_status_code
is similar, but not the same as anorder_status_code
. They shouldn't be in the same column.