Hi, just had few design questions about a database im working on.
1. The coffee and tea tables both have product_upc as a primary key, but this seems to be a problem when trying to use them both as a foreign key for inventory.
2. I was wondering if there are going to be possibly thousands of users per group, around 10 stores per group and around 300 inventory entries per store if i should make a seperate db for each group. The reason in had them in one db was because i wanted to keep the detailed product info together so it would be easy to update.
Alright so then would these cardinalities be right?
What im trying to describe in the diagram is a price reporting system. Where each group represents a different geographic location and Where Inventory is a collection of all the prices and corresponding product_upc's, user_id's, and store_id's reported from every store in every region.
So there may be thousands of coffee and tea entries with only a hundred associated with the inventory table because not all will have prices reported by a user. And there may be multiple of each product in inventory because it could be reported at more than one store.