I am in the process of setting up tables in my database for my first project. (Exciting!)
I am having a hard time deciding what types of relationships I need to set up.
I have the following basic tables planned.
products
-----
id
product_name
product_details
product_url
product_img
category_id
business_id
categories
-----
id
category_name
category_description
category_slug
businesses
----------------
id
business_name
business_phone
business_address
business_city
state_id
business_zip
state
-----
id
state_name
Where I am stuck is deciding what types of relationships to set up.
Each product can only belong to 1 category, and can only belong to 1 business
As for the business table I am wondering if its a better idea to break out the city and zip-code into separate tables.
I want to be able to retrieve products by category and city , For example: 'shoes' in 'los angeles', or just 'shoes' or all products in 'los angeles'
Can anyone offer some insight or share their experience. Since I am getting ready to set up my tables I'd rather work out those scenarios now then half way through development.
Your design is OK - it's pretty clean. I can't see a many-to-many anywhere - it seems to be a straight hierarchy.
Also, your thinking process seems clear - keep asking yourself these kinds of questions and you'll be fine.
However, I have these suggestions:
Firstly, always name your tables in the singular, business not businesses.
Secondly, try to avoid prefixing the table name to column names, so name, not business_name - when you reference it in a query, it's obvious anyway: business.name (the extra business_ in business.business_name is redundant)
Also, because zip is in a city, and city is in a state, storing city and state on business is redundant data, so you should probably do this:
business
----------------
id
name
phone
address
zip_code_id
zip_code
--------
id
city_id
name
city
----
id
state_id
name
state
-----
id
name
To answer your questions re queries, you can get what you need with this schema. I won't post it here unless you really have trouble, but they are very simple queries, so I'll leave that for you to work out.