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
Change is inevitable... Change for the better is not.