I'm trying to figure out something with a database I'm currently trying to structure, but instead of getting into that specifically, I'll use a simple example to demonstrate the trouble I'm encountering.
Let's say I have a database for keeping track of orders of fruit. I'll have an Orders Table, with OrderID, CustomerID, Fruit, quantity... stuff like that. For the fruit, I want to categorize them as apples, oranges, and grapes. Then, each subcategory of fruit could be broken down further, with varieties of apples, varieties of oranges, and varieties of grapes.
Feasibly, each type of fruit would have a table, and each variety of fruit would be a record in that table. The different types of fruit (apples, oranges, and grapes) could be kept track of in another table, and each fruit table could have field that is the same for every record. That would provide a simple way of keeping track of the total number of tables there are for types of Fruit.
Here is a diagram of the tables for this example database:
The problem I am having is this: If I want to add a new apple variety in the Apples table (preferably with a form), how do I make a new FruitID to use for the new record that will be added to the Apples table? Forms are pretty much restricted to one record at a time. There would probably be a way to do this with VBA, but can't help but feel I'm missing a simpler solution somewhere.
I could just add a new record to the Fruit table first, and then use the newly generated ID, but because FruitID has a one-to-one relationship with the records for fruit varieties in all of the individual fruit tables, that seems like it creates the potential of generating extraneous FruitID records that don't directly tie to a record in one of the tables for each type of fruit.
Are there significant drawbacks to however would try to implement this? Does this table structure itself need to be reexamined?
Any thoughts and feedback are appreciated.