05-04-15, 17:59 #1Registered User
- Join Date
- May 2015
Unanswered: Need help with designing a database to handle product specifications
Hello, I'm looking for some guidance regarding a database I am designing in Access. This is the first database I have tried to build and am already stuck.
One of the most important parts of the database is a product list, and I want to know the best way to store the information. I will simplify the scenario to make it easier to understand.
Let's say I am building the database for a company that produces a line of granola bars. They make about 50 unique types of granola bars, most are very similar but nonetheless unique in some way. Naturally, I will have a Granola Bar ID Number as a primary key and a Granola Bar ID Name for each type. Additionally, each type of granola bar requires multiple tests that are performed to ensure top quality for the customer. There are 20 different tests that can be performed such as protein %, salt content, taste score, density, # of bacteria cultures, etc. Each test for a specific granola bar has its own min, max, and target specification.
Granola Bar A
Test 1: Protein % / Target 15 / Min 5 / Max 20
Test 2: Salt content / Target 2 / Min 1 / Max 3
Test 3: Taste Score / Target 10 / Min 7 / Max ---
No other tests
Granola Bar B
Test 1: Protein % / Target 20 / Min 10 / Max 25
Test 2: Salt content / Target 2 / Min 0.5 / Max 2.5
Test 3: Density / Target 8 / Min 5 / Max 12
Test 4: # of bacteria cultures / Target 0 / Min --- / Max 3
No other tests
The problem I'm running into is that each product can have a different number of tests and each test can have a different target, min, and max depending on the product.
The only way I can think about organizing it is just having a single table with many fields: Protein%Target, Protein%Min, Protein%Max, etc. for every test and product. But there has to be a better way, right?
It's almost a hierarchy: Product->Test->Specs; and I don't know how to design a relational database that holds this information.
I don't want to complicate the main question too much, but the product table will later be linked to other tables in the database, like tables showing how many batches were produced of a product, the conditions it was produced at, etc. Eventually, I want to use this database to track quality failures. For example, if Granola B tests with a density of less than 5, I want to record an instance of that and keep track of it. Then I'd like to generate at least four queries/reports:
1. A general report where the product can be selected and all the specifications listed.
2. Within a specified date range, how many times did a certain test fail and for what products?
3. Within a specified date range, how many products failed quality and on what tests?
4. Within a specified date range and for a certain product, how many times was there a quality failure?
I apologize for asking such a general design question, but even after reading a lot about database design and normalization, I can't figure out how to start.
Thanks for any help you can give!
05-04-15, 18:40 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
id probably look at some form of templating such as the attachment
metric type indicates how the result should be displayed (eg as a percent, integer, double, text whatever... will need refining, espceially for a text result!
test types defiens the type of texst (eg protein, carbo, fat etc....
prodcuts, self explanitory
product_test_profiles defines the hard limits (min and max values (say fpor a % value cannot be less than 0 nor hgher than 100)
a lower limit (ie must not be less than x units
an upper limit cannot be more than x units
..again will need refinign to represent your testign regime (you may need to use the sub/super type model for the test profile eg store pass/fail results in one siper table, % in another, intewger counts another and so on)
having defined the test profiling then product tests identifies a specific test run on a specific sample of a product
what you are trying to achieve is a rules system where you define what are sane values (min & max values), you may need to also allow a column to say if nulls are acceptable (but I can't see the logic behind a null value in this sort of application)
the limit values are hard values, to me lower limit means must not drop below this value (so if there is no lower limi tthen it should be the same as the minimum value OR NULL), the upper limit means sample must not exceed this value, and therefore if not defined should be the same as the maximum acceptable value
having defined your rules then you can move on the data capture and from that you know what trests have passed or failed
..but thats a quick and dirty possibnle approach it may not sensible for your application
it may not scale particuraly well.
whatever you do do, avoid any temptation to use the EAV model.I'd rather be riding on the Tiger 800 or the Norton
05-04-15, 22:05 #3Registered User
- Join Date
- May 2015
Thanks for the quick reply! I have only taken a quick look at the example, but it already is helpful.
I might have more questions once I start working on it again, though!