Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Design Best Practice Question Expand / Collapse
Author
Message
Posted Friday, May 30, 2014 9:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 8:52 AM
Points: 4, Visits: 80
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


  Post Attachments 
dbdiag20140530.xlsx (8 views, 11.89 KB)
Post #1576322
Posted Friday, May 30, 2014 11:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 37,099, Visits: 31,651
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1576338
Posted Saturday, May 31, 2014 8:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 8:52 AM
Points: 4, Visits: 80
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?
Post #1576400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse