MySQL Database Design: one to one,many to many, many to one, or too many?

I am in the process of setting up tables in my database for my first project. (开发者_如何学GoExciting!)

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.

You should weigh the advantages and disadvantages.

With many to many relationships, if you normalize then you end up with more tables and would require more joins. If you have to retrieve these records very regularly joins would be very expensive and add a lot of overhead.

If you decide to put things into one big table then you are increasing data redundancy, which wastes storage space, but is worth it if that combination of columns are always queried because of better index usage and no joins. It however increases application development complexity. This is especially the case when you have the same columns in different tables due to denormalization and end up having to remember updating both tables, which increases the risk of data inconsistencies if you forgot to update either one.

In conclusion

So it really depends on your situation, if preformance is key and you don't mind the increased complexity and possible data integrity issues, then take on a denormalized approach.

If however performance is not a big issue (not a lot of rows, not a lot of users, more than enough hardware, speed is not concern) then separating the relationship tables would decrease storage space (pointless these days since its so cheap), increase data integrity, decrease data inconsistencies and decrease development complexity