Structure of the DB

Question

I am trying to structure a database for optimal use. But I have no idea how to make that happen. Here is what i will have

A category which will have many products and a count for each on of those products

For example

Restaurants will be the category and the products will be the computer system, cash register and each one of these products has a count so maybe there will be 2 cash registers and 3 computer systems and so on. Another catagory might be a bar, which might or might not have the same products. Then the user has the option of choosing a 2 restarant package which might have a discount rate for getting all the products.

I just need to structure the ERB if i can get some help, Thanks

Answer 1

PRODUCTS

• PRODUCT_ID (primary key)
• PRODUCT_NAME

CATEGORIES

• CATEGORY_ID (primary key)
• CATEGORY_NAME

PRODUCT_CATEGORIES_MAP

• PRODUCT_ID (primary key, foreign key to PRODUCTS)
• CATEGORY_ID (primary key, foreign key to CATEGORIES)
• QUANTITY

This'll allow you to associate a product to more than one category, but not allow duplicates.

Answer 2

Establishment

• ID
• Name
• <address info>

Product

• ID
• Name

Estab_Prod_Bridge

• ID_Product
• ID_Establishment
• Quantity

Discounts

• ID_Establishment
• Percent_Purchase
• Discount

---

Note:

1. Establishment is a long word, I just used it here for example. You could easily replace it with location/loc, venue, or anything else you see fit.

2. Depending on how the data changes, I added the discount table, thinking there would be different discounts for variant amount of products purchased.

   Example: 80% of products purchased would yield a 5% discount, but 100% of purchases might yield a 10% discount.