Best practices for database tables.
-
Hello All, First time using a database of any kind. I am using SQLite with a VB.net application. Currently I store/retrieve data from an excel workbook and I want to replace that method with the database. I have the code working to generate the database tables but want some expert opinions on best practices when laying out my tables. In the workbook I have a sheet for each drawer in several large containers 10 containers with 100 drawers each. Each drawer holds bins of different parts for a specific machine so each one is unique. So my excel sheet looks like this: "Sheet Name = Container_1_Drawer_1" "Sheet Row 1 = Pins" "Sheet Row 2 = Clips" ... "Sheet Row 100 = 1/4-20 x 1 Bolt" My first though is to have a table for the drawer that is referenced by a table of containers but having no exp with databases I am not sure if this is the best approach or if I could do something more elegant. Thanks in advance.
-
Hello All, First time using a database of any kind. I am using SQLite with a VB.net application. Currently I store/retrieve data from an excel workbook and I want to replace that method with the database. I have the code working to generate the database tables but want some expert opinions on best practices when laying out my tables. In the workbook I have a sheet for each drawer in several large containers 10 containers with 100 drawers each. Each drawer holds bins of different parts for a specific machine so each one is unique. So my excel sheet looks like this: "Sheet Name = Container_1_Drawer_1" "Sheet Row 1 = Pins" "Sheet Row 2 = Clips" ... "Sheet Row 100 = 1/4-20 x 1 Bolt" My first though is to have a table for the drawer that is referenced by a table of containers but having no exp with databases I am not sure if this is the best approach or if I could do something more elegant. Thanks in advance.
My gut feel says that you need to have a table for the parts themselves - I doubt there is only one machine that uses Pins or Clips or that size bolt.
Part
- PartID - a unique identifier (int or guid)
- PartName
- Other information as required
Parts go into Bins and I'm assuming here that each bin can only contain one type of Part so
Bin
- Bin_ID - unique identifier
- Drawer_ID - Foreign key to Drawer table
- Part_ID - Foreign key to the Part table
- Information about the bin e.g. number of parts in this bin
Bins go into Drawers but note that the Drawer table doesn't reference the Bins, the Bins reference the Drawer, it's "owner" if you like. The same applies to the Drawer referencing it's Container
Drawer
- Drawer_ID
- Container_ID - Foreign key to Container table
- Other info about the Drawer e.g. Machine reference?
Container
- Container_ID
- Other info about the container e.g. location
It's very easy to over-engineer database design. For example I'm tempted to introduce a Machine table but that's probably over-doing it. You might find these links helpful too: Database Design Tutorial for Beginners - William Vincent[^] How to Design a SQL Database[^]
-
Hello All, First time using a database of any kind. I am using SQLite with a VB.net application. Currently I store/retrieve data from an excel workbook and I want to replace that method with the database. I have the code working to generate the database tables but want some expert opinions on best practices when laying out my tables. In the workbook I have a sheet for each drawer in several large containers 10 containers with 100 drawers each. Each drawer holds bins of different parts for a specific machine so each one is unique. So my excel sheet looks like this: "Sheet Name = Container_1_Drawer_1" "Sheet Row 1 = Pins" "Sheet Row 2 = Clips" ... "Sheet Row 100 = 1/4-20 x 1 Bolt" My first though is to have a table for the drawer that is referenced by a table of containers but having no exp with databases I am not sure if this is the best approach or if I could do something more elegant. Thanks in advance.
Pick a database structure that reflects your requirements and study the layout. http://www.databaseanswers.org/data_models/[^]
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Pick a database structure that reflects your requirements and study the layout. http://www.databaseanswers.org/data_models/[^]
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP