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
每个产品只能属于1个类别,并且只能属于1个业务
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.
任何人都可以提供一些见解或分享他们的经验由于我正准备设置我的表,所以我宁愿在现有的情况下解决这些问题。
2 个解决方案
#1
3
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)
其次,尽量避免将表名前缀为列名,因此name,而不是business_name - 当你在查询中引用它时,无论如何它都是显而易见的:business.name(business.business_name中的额外business_是多余的)
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:
此外,由于zip位于城市中,而城市处于某种状态,因此将城市和州存储为业务是冗余数据,因此您应该这样做:
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.
要回答您的问题,您可以通过此架构获得所需的内容。我不会在这里发布,除非你真的遇到麻烦,但它们是非常简单的查询,所以我会留给你解决。
#2
-1
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
然而,如果性能不是一个大问题(不是很多行,没有很多用户,硬件足够多,速度不受关注)那么分离关系表会减少存储空间(这些天以来它很便宜),提高数据完整性,减少数据不一致性并降低开发复杂性
#1
3
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)
其次,尽量避免将表名前缀为列名,因此name,而不是business_name - 当你在查询中引用它时,无论如何它都是显而易见的:business.name(business.business_name中的额外business_是多余的)
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:
此外,由于zip位于城市中,而城市处于某种状态,因此将城市和州存储为业务是冗余数据,因此您应该这样做:
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.
要回答您的问题,您可以通过此架构获得所需的内容。我不会在这里发布,除非你真的遇到麻烦,但它们是非常简单的查询,所以我会留给你解决。
#2
-1
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
然而,如果性能不是一个大问题(不是很多行,没有很多用户,硬件足够多,速度不受关注)那么分离关系表会减少存储空间(这些天以来它很便宜),提高数据完整性,减少数据不一致性并降低开发复杂性