Nested 1-to-many relationships appropriate? codeable?

[sniffingvixen]

Tables[li]ProductDetails: Primary table with a text-based primary key (ProjectID).[/li]
[li]pdProduceItems: Secondary table with an autonumber-based composite primary key (ProjectID + ProduceItemID).[/li]
[li]pdOnions: Tertiary table with an autonumber-based composite primary key (ProjectID + ProduceItemID + OnionID)[/li]

Desired Relationships
[li](one)ProductDetails.ProjectID ---> (many)pdProduceItems.ProjectID[/li]
[li](one)ProductDetails.ProjectID ---> (many)pdOnions.ProjectID[/li]
[li](one)pdProduceItems.ProduceItemID ---> (many)pdOnions.ProduceItemID[/li]

Current Implementation
[li](one)ProductDetails.ProjectID ---> (many)pdProduceItems.ProjectID[/li]
[li](one)pdProduceItems.ProjectID & pdProduceItems.ProduceItemID ---> (many)pdOnions.ProjectID & pdOnions.ProduceItemID[/li]

[ol]Questions
[li]Is this acceptable practice?[/li]
[li]Can I automatically create a new record in pdOnions when pdProduceItems.ProduceItemType is changed to "Onion"?[/li]
[li]If so, can I expand the same code to create various numbers of new records according to the value of pdProduceItems.ItemOptionsAvailable?[/li]
[li]Or, can a field like pdOnions.OnionOptionsAvailable do the job more effectively?[/li]
[li]Can similar coding be used to limit the number of records that can be created?[/li]
[/ol]

I know these are all "yes or no" questions, but, if anyone can give me a clue about the "how" and "why" too, I would really I appreciate it.

Thanks,
j

 

[rac2]

(one)ProductDetails.ProjectID ---> (many)pdOnions.ProjectID

This relationship strikes me as unnecessary. Because of the one-many relationships of ProductDetails with ProduceItems and ProduceItems with pdOnions, you get a one-many for ProductDetails with pdOnions.

"Automatic" is a relative term. In SQL Server you would write a trigger that would insert rows of onions whenever a row is inserted in items. And this trigger could lookup the appropriate number of rows in an options table. A trigger is a stored procedure that runs whenever a table is updated, deleted, or inserted. I do not know how or whether that could be done in Access. At the very least, I suspect there would be a way to write a VBA module to achieve the same thing. But it would not be something that could be done by pointing and clicking.

An ItemOptionsAvailable column seems like the way to go. It just depends on whether the number of rows to insert depends on the item or the onion. An ItemOptionsMax could be used to limit the number inserted.

HTH

 

[sniffingvixen]

Thanks, rac2, your comments make sense and I have streamlined my relationships accordingly. But I think I may have bigger problems/confusions...

First question (that seems like it should be an FAQ, but maybe I'm just overthinking things): Whenever a lookup query is used to populate a list of combobox values, should the lookup field and the combobox field have a one-to-many relationship?

Next, I think I've achieved 1NF, but I can't figure out the best way to implement 2NF. The quote below describes some of our needs. Following that I've abbreviated my current table structure.

We have a variety of products for which we are testing consumer interest and liking. Each product may require review of individual responses or compiled data, depending on what's available. In addition to reviewing a single product's test results, we want to be able to compare products with similar builds.

Product builds vary a great deal. From drinks to sandwiches, salads to desserts, each type of product will be compared according to different criteria. With only two points of comparison, drinks are easy. We compare the type of drink, like coffee or shake, and the default toppings, like whipped cream and cherry.

Sandwiches, on the other hand, are complicated. First, we look at the type of bread a sandwich comes on, like sourdough or pita and the type and number of proteins that it includes, like hamburger or egg and bacon or deli meat. Second, we look at cheese: how many slices of cheese come on the sandwich, how many different types of cheese are offered with the sandwich, and what types are they? Third, we look at produce: what produce items are included by default? Fourth, we define each produce item: how many varieties are offered with the sandwich, how many different varieties are included on one sandwich (actually this applies more to salads), and what types are they? Finally, we want to know the same things about sauces, condiments, and toppings.

The following set-up is representative of my entire database design, where tables are bold, primary keys are underlined, and relationships (assuming a "yes" answers my first question) are shown with colored numbers and "m's": red for lookups and blue for others.

ProductDetails.ProjectID1

pdProduceItems.ProjectID1m
pdProduceItems.ProduceItemID2
pdProduceItems.ProduceItemsPerOrder
pdProduceItems.ProduceItemType3m

pdProduceItemTypes.ProduceItemType3

pdOnions.ProduceItemID2m
pdOnions.OnionID
pdOnions.TotalOnionOptions
pdOnions.OnionTypesPerOrder
pdOnions.OnionType4m

pdOnionTypes.OnionType4

So, I see that ProduceItemsPerOrder, TotalOnionOptions, and OnionTypesPerOrder aren't dependent on the PK's within their tables, but I don't know what to do with them.
[li]Should I move them to ProductDetails because they are strictly dependent on ProjectID?[/li]
[li]But, if I do that, won't I be violating 1NF again by having repetitive groups in ProductDetails?[/li]
[li]How about replacing numeric fields with one that shows whether each component is "one type of several options" or "always included by default"?
[li]But, if I do that, don't I still have a problem with a burger that has two cheese slices each one of which can be cheddar or mozzarella or swiss?[/li]

If you've gotten this far, thanks for considering my entire situation. Any guidance will be greatly appreciated. -j

 

[rac2]

Whenever a lookup query is used to populate a list of combobox values, should the lookup field and the combobox field have a one-to-many relationship?

Yes? A combobox is not a database element. It does not have a relationship to anything in a database.

Lookup tables can be the source of data for a combobox, or dropdown menu. A lookup table typically has at least two columns, a code and a description. These are used for the menu. The table may be useful in other displays where you wish to show the description, but the code (key) has been stored. And there may be a variety of facts that go along with the code and description which can be useful in a variety of displays and conditional processing.

ProduceItemsPerOrder, TotalOnionOptions, and OnionTypesPerOrder aren't dependent on the PK's within their tables

To re-state in my own words.
ProduceItemsPerOrder does not depend on the composite key
ProjectID and ProduceItemID. So, for a given ProjectID and ProduceItemID, the value of ProduceItemsPerOrder is not known? That is, there may be two rows with the same pair of IDs and with different values of ProduceItemsPerOrder?

Likewise for TotalOnionOptions and OnionTypesPerOrder. If we know the values of ProduceItemID and OnionID we may not know the values of TotalOnionOptions and OnionTypesPerOrder?

I think that dependent means if I know the value of the key, then I know, from that particular row in the table, all of the other values. But I could be wrong because I dont usually think about table design that way. To be honest, I would need to get out a textbook to refresh 2NF.

I think about what kind of thing or situation or event is recorded in the table. The table has columns which describe that kind of thing. In other words, attributes. Or the column points to another kind of thing which has a relationship to this thing, a foreign key.

I have trouble thinking about your design because I get confused by a couple of naming choices. It appears that ProjectID is the primary key for ProductDetails. I would use that column name in a table of Projects, and I would use ProductDetailsID as the name of the key column in ProductDetails. And I have this feeling that the Onions table doesnt record only onion; that it may also record cheeses, condiments, and garnishes. And why a pdOnions table instead of simply Onions. I confess this reflects rather concrete thinking.

But then I never put cheese on my burgers; strictly grilled onions, or lettuce, tomato, and pickle.

 

[sniffingvixen]

I think that dependent means if I know the value of the key, then I know, from that particular row in the table, all of the other values. But I could be wrong because I dont usually think about table design that way. To be honest, I would need to get out a textbook to refresh 2NF.

To re-state in my own words.
ProduceItemsPerOrder does not depend on the composite key
ProjectID and ProduceItemID. So, for a given ProjectID and ProduceItemID, the value of ProduceItemsPerOrder is not known? That is, there may be two rows with the same pair of IDs and with different values of ProduceItemsPerOrder?

My understanding of 2NF dependency is that non-key column values are implied by their entire associated primary key. In other words, for the composite key ProjectID+ProduceItemID, ProduceItemType is fully dependent because it describes the particular item identified by the key. But ProduceItemsPerOrder isn't because it can be determined from the ProjectID alone.

...Wait...I think I figured it out!

I need to change the primary keys so that the number of items allowed and the number of options for those items (if applicable) are defined by them!

So, for pdProduceItems, the new key will be ProjectID+ProduceItem# with ProduceType as the only dependent column.
And for pdOnions, the new key will be ProjectID+Item#+Option#.

Yay! Thanks for helping me think this through!

I have trouble thinking about your design because I get confused by a couple of naming choices. It appears that ProjectID is the primary key for ProductDetails. I would use that column name in a table of Projects, and I would use ProductDetailsID as the name of the key column in ProductDetails.

Yes, I see what you mean. Originally, we thought we could use product numbers as primary keys. When I realized that wouldn't work, I changed the key but not the table. Thank you for bringing this to my attention. ProjectDetails is a far more apt description because the table covers product build and test parameters.

And I have this feeling that the Onions table doesnt record only onion; that it may also record cheeses, condiments, and garnishes.

I'm sorry I wasn't very clear. In the interest of brevity, I tried to represent my design through the fewest possible tables. Be assured that I have a separate table for cheese, pickles, lettuce, etc. I did actually wrap sauces/condiments and toppings/garnishes into one table because we don't need to separate them by kind.

And why a pdOnions table instead of simply Onions.

This really is a subjective point, I hope? My overall design has three main tables (ProjectDetails, CompiledData, and IndividualResponses). All other tables have two-letter prefixes that show which is their main table. I won't be finishing the database, so I wanted to create a system of identification that would make the relationships instantly obvious to whoever does.

BTW, CompiledData is NOT related to or calculated from IndividualResponses.

Thanks again for your patience and your helpful suggestions!