Database Design Best Practice Question

Question

Database Design Best Practice Question

SQL Shibe

SSC Veteran

Points: 218
More actions
May 30, 2014 at 9:40 pm

#307785

I am building out a database (please see sample attachment) and I am second guessing myself on my database design and would like to get a second opinion on this. I have a primary table that stores a bunch of integers that are foreign keys to smaller tables that will be accessed by drop down fields in the application. For every drop down field there will be a small table that will be referenced by the primary table based on the integer ID field. Is this the best way to do this? When an entry is made the integer ID will be populated in the primary table based on what is selected from the drop down menus.
Note: "Active" is a y/n column showing whether or not the user is still employed. The user will be changed to "n" if they are no longer with the company. This is in order to keep referential integrity with historical data. I am building this from the ground up and I want to make sure I do this right the first time. I will be coding the front end application once the database build is complete.
Thanks for the assistance,
SQL Shibe

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

SQL Shibe (5/30/2014)
I am building out a database (please see sample attachment) and I am second guessing myself on my database design and would like to get a second opinion on this. I have a primary table that stores a bunch of integers that are foreign keys to smaller tables that will be accessed by drop down fields in the application. For every drop down field there will be a small table that will be referenced by the primary table based on the integer ID field. Is this the best way to do this? When an entry is made the integer ID will be populated in the primary table based on what is selected from the drop down menus.
Note: "Active" is a y/n column showing whether or not the user is still employed. The user will be changed to "n" if they are no longer with the company. This is in order to keep referential integrity with historical data. I am building this from the ground up and I want to make sure I do this right the first time. I will be coding the front end application once the database build is complete.
Thanks for the assistance,
SQL Shibe

My first instinct would be to change the names of all the "ID" columns to follow the standard of tablenameID

Also, if the ScrapCode table is only a reference table, then I wouldn't use an ID on that table. I'd limit it to just the code and use the code in the Primary table. Humans will like you better for it when they have to read the Primary table.

Also, will each BatchNumber in the Primary table only be for 1 ItemID? If not (and they probably shouldn't be), then consider having a separate Batch table and Item table.

I think you also need a RunType table to ensure the quality of the data entered into that column in the Primary table.

Append "ID" to the StartOperator and EndOperator columns.

Consider NOT abbreviating the word "Description" in the Recipe table.

"TimeInSolution" is fairly non-descript. How will you identify such a duration in the table. Decimal hours, minutes, seconds, hhhhhhhh:mi:ss, fortnights, or what??? The same applies for TotalRunTime. WHAT will the unit of measure be for these two columns? That will allow us to define more descript column names.

Consider NOT using the Nbr abbreviation in WorkOrderNbr in the Primary table. Either replace it with the '#" sign or spell it out so people don't have to guess which abbreviation you may have used.

Consider NOT using columns called "Active". If they are guaranteed to only contain the two obvious states, consider calling them "IsActive". But, to bullet-proof your code for the future, consider using "StatusCode" instead and add a StatusCode table for DRI.

On the start and end operator thing. What if you have 3 operators? Consider logging each operator in a separate table along with their start and end DTs.

Consider moving comments to a separate table with DRI to the Primary table so you can keep track of who made what comment and when.

Consider adding a StatusCode column to every table.

Consider adding a CreatedOn and CreatedBy column to every table. Same goes for a ModifiedOn and ModifiedBy column. It seems like a grand waste of time and space but unless you have a really good audit system running, it'll save your sanity in the future. Depending on the attention to detail that your Developers put into their code, consider adding a trigger to the tables to ensure that ModifiedOn and Modified by will be filled in if by nothing else other than GETDATE() and ORIGINAL_LOGIN(), respectively.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Shibe SSC Veteran Points: 218 More actions · Answer 2

Sorry it took so long to get back to you, thank you for all your suggestions. I am sure it seemed like a fortnight, haha.

My first instinct would be to change the names of all the "ID" columns to follow the standard of tablenameID

I agree, I have changed the ID columns to reflect the tablenameID. This will hopefully make things easier when making the FK relationships.

Also, if the ScrapCode table is only a reference table, then I wouldn't use an ID on that table. I'd limit it to just the code and use the code in the Primary table. Humans will like you better for it when they have to read the Primary table.

The scrap code will be referenced by more than one table, one of the tables I didn't get a chance to list. So there are scrapped jobs from the primary table but in the jobs there are solutions which can also be scrapped. The scrap code will be a drop down field that will say for example:

ScrapCodeID | ScrapReason

1 | Caught Fire

2 | Defects in raw materials

ect...

Also, will each BatchNumber in the Primary table only be for 1 ItemID? If not (and they probably shouldn't be), then consider having a separate Batch table and Item table.

The batch ID follows the product through the whole process, now it starts off with a few letters / numbers and then expands as it goes through the process. The batch ID will be referenced when we need to trace the processes all the way back to the beginning.

Ex:

Step1: ABC-1234

Step2: ABC-1234-05

Step3: ABC-1234-05-01

Step4: ABC-1234-05-01-101

I think you also need a RunType table to ensure the quality of the data entered into that column in the Primary table.
Append "ID" to the StartOperator and EndOperator columns.
Consider NOT abbreviating the word "Description" in the Recipe table.

I have added a run type table and changed the RunType to RunTypeID. I have also updated the Start and End operator IDs and the word description has been fully spelt out now.

"TimeInSolution" is fairly non-descript. How will you identify such a duration in the table. Decimal hours, minutes, seconds, hhhhhhhh:mi:ss, fortnights, or what??? The same applies for TotalRunTime. WHAT will the unit of measure be for these two columns? That will allow us to define more descript column names.

The TimeInSolution will be a drop down where an operator can select the time based on a set incremental between 5 and 30 minutes. (5min, 10min, 15min, 20min, 25min, 30min) I tried to use fortnights but they shot that idea down. Now the TotalRunTime, will be a calculated time based on the StartDT and EndDT. (It will be used for analytics later on.)

Consider NOT using the Nbr abbreviation in WorkOrderNbr in the Primary table. Either replace it with the '#" sign or spell it out so people don't have to guess which abbreviation you may have used.

I have changed the Nbr to Number.

Consider NOT using columns called "Active". If they are guaranteed to only contain the two obvious states, consider calling them "IsActive". But, to bullet-proof your code for the future, consider using "StatusCode" instead and add a StatusCode table for DRI.

Just so I know I understand this correctly the StatusCode would be a table with 2 rows correct? Then the ID of either Active/Inactive would be an ID field in each of the tables? Sorry if I misunderstood!

On the start and end operator thing. What if you have 3 operators? Consider logging each operator in a separate table along with their start and end DTs.

There will only be 2 operators on every project, since the process takes up to 14 hours to run there are only 2 shifts for the operators. So one starts it at 6am and another operator ends it before the end of their shift.

Consider adding a CreatedOn and CreatedBy column to every table. Same goes for a ModifiedOn and ModifiedBy column. It seems like a grand waste of time and space but unless you have a really good audit system running, it'll save your sanity in the future. Depending on the attention to detail that your Developers put into their code, consider adding a trigger to the tables to ensure that ModifiedOn and Modified by will be filled in if by nothing else other than GETDATE() and ORIGINAL_LOGIN(), respectively.

Thank you so much for this suggestion! It makes perfect sense even if it is a little more work up front.

Now in the primary table has multiple integer fields based on the ID fields when selected in from the drop down menus. Is this still an okay way to do things?