Structure 3 Tables for Parts or just one
Structure 3 Tables for Parts or just one
(OP)
Tek-Tips Pro's
A little background....
I could really use some direction; we are building a custom database for my business, MightyPouch.com. We make custom cases for just about everything from scratch. We cut the materials and sew them.
Our business model is to offer the customer a completely customizable case, if the specify it's for an Iphone, the database will pull the dimensions for the Iphone and production will receive a cutlist for the job with all the materials the need. The customer can order just on dimensions too ie a phone with a hard case we build from dimensions.
I am trying to design a full database, from order importing from the website to printing a packing list and managing payments.
The question
We have a debate ongoing on the tables and relationship structure.
I may be over splitting tables. My Coworker and I are stuck in a debate as to how to organize our database structure. I am suggesting that all 3 component types be split in to 3 tables as the relationships attached show.
He is suggesting that all 3 of these tables be consolidated to one table even though they do not always share the same fields.
Who is right? My argument is that the 3 materials have different needs and all production lists and screen layouts will have sorted/grouped components. As we gather materials in different locations this will make it easier on production.
He argues that I simply need a field in the main table that is has the option of "1DPart", "2DPart", and "Component". This would allow me to sort the items for a cleaner layout for production.
He believes the seperate tables will bloat the database as 3 tables would have to be opened to add each part needed. I argue that the 3 tables will each be 1/3rd the size of his one table.
Other issues to consider
We will have code that when a line item is entered with a certain option the part/material is added to the build lists automatically.
In my opinion these parts will be added to the 3 separate tables. He wants them added to just one.
We will need a full inventory system, and the plan is to store the consumed materials for each line item and the acquired materials in a separate table. Inventory will be based on the calculation, total consumed – total acquired.
Your insight and direction would be appreciated. I have attached images of the current relationships for help in describing the situation. Sorry if this was to much information, just trying to get you all the information you might need. Our needs are definately not standard with the custom product line we offer.
We would appreciate your insight.
Thank you for your time,
Scott Holmes
MightyPoch.com
The 3 Tables in question...
Example of form view showing 3 seperate tables
The full relationships of database, still need inventory and payments.
A little background....
I could really use some direction; we are building a custom database for my business, MightyPouch.com. We make custom cases for just about everything from scratch. We cut the materials and sew them.
Our business model is to offer the customer a completely customizable case, if the specify it's for an Iphone, the database will pull the dimensions for the Iphone and production will receive a cutlist for the job with all the materials the need. The customer can order just on dimensions too ie a phone with a hard case we build from dimensions.
I am trying to design a full database, from order importing from the website to printing a packing list and managing payments.
The question
We have a debate ongoing on the tables and relationship structure.
I may be over splitting tables. My Coworker and I are stuck in a debate as to how to organize our database structure. I am suggesting that all 3 component types be split in to 3 tables as the relationships attached show.
He is suggesting that all 3 of these tables be consolidated to one table even though they do not always share the same fields.
Who is right? My argument is that the 3 materials have different needs and all production lists and screen layouts will have sorted/grouped components. As we gather materials in different locations this will make it easier on production.
He argues that I simply need a field in the main table that is has the option of "1DPart", "2DPart", and "Component". This would allow me to sort the items for a cleaner layout for production.
He believes the seperate tables will bloat the database as 3 tables would have to be opened to add each part needed. I argue that the 3 tables will each be 1/3rd the size of his one table.
Other issues to consider
We will have code that when a line item is entered with a certain option the part/material is added to the build lists automatically.
In my opinion these parts will be added to the 3 separate tables. He wants them added to just one.
We will need a full inventory system, and the plan is to store the consumed materials for each line item and the acquired materials in a separate table. Inventory will be based on the calculation, total consumed – total acquired.
Your insight and direction would be appreciated. I have attached images of the current relationships for help in describing the situation. Sorry if this was to much information, just trying to get you all the information you might need. Our needs are definately not standard with the custom product line we offer.
We would appreciate your insight.
Thank you for your time,
Scott Holmes
MightyPoch.com
The 3 Tables in question...
Example of form view showing 3 seperate tables
The full relationships of database, still need inventory and payments.
Talk To Other Members
RE: Structure 3 Tables for Parts or just one
Duane
Hook'D on Access
MS Access MVP
RE: Structure 3 Tables for Parts or just one
Think of it this way, if you need a report with all the data (or to just look at all the records at onece) one day you don't want to go to three different places.
At work now, the guy who's taking care of the backend has a different table for each and every type of dataset. It's a mess and it's impossible to find the data. That's the whole point of having a table, to keep everything in one place.
-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor
RE: Structure 3 Tables for Parts or just one