I am developing a web application where users can create the following resources/contents:
我正在开发一个Web应用程序,用户可以在其中创建以下资源/内容:
Events | Music | Posts | Classifieds
They have alot of fields in common, such as:
他们有很多共同的领域,例如:
created_date | title | desc | user_id
Now I am wondering if I should create separate tables for each content, or save them all in one table, with a type_id foreign key, which points to a content_type table. Ofcourse, some distinct fields will be there which will be only used by specific content types, for those not using those fields, I can just leave it blank.
现在我想知道是否应该为每个内容创建单独的表,或者将它们全部保存在一个表中,并使用type_id外键,该外键指向content_type表。当然,一些不同的字段将只存在于特定的内容类型中,对于那些不使用这些字段的人,我可以将其留空。
Data looks more organized with separate tables for each content type, but searching for a keyword across all tables is becoming a nightmare(with joins, unions etc). If it was just a single table, searching will be very easy.
对于每种内容类型,数据看起来更加有条理,但是在所有表中搜索关键字正成为一场噩梦(包括联接,联合等)。如果它只是一个表,搜索将非常容易。
I need that the user be able to search across all content with a keyword. He would also be able to search specific contents, for that I will do a WHERE clause on the type_id field.
我需要用户能够使用关键字搜索所有内容。他也可以搜索特定内容,因为我将在type_id字段上执行WHERE子句。
I am not aware of all the pros/cons of each method, but I would appreciate if people could advice me so that I don't make the wrong decision, and have to redo everything from start.
我不了解每种方法的所有优点/缺点,但如果人们可以建议我这样做,我就不会做出错误的决定,并且必须从头开始重做所有内容,我将不胜感激。
2 个解决方案
#1
0
Unless they truly have identical data, I would use separate tables. Having one table with some fields only used by specific content types is really not very good database design.
除非他们真的拥有相同的数据,否则我会使用单独的表格。有一个表只有特定内容类型使用的某些字段,实际上并不是很好的数据库设计。
If you really want one table with the basic data, you could create one as you suggested with a content_type and the common fields, and then have 4 separate tables for each of the types with the other distinct fields, then do an inner join when you select the fields for that type. But personally I think you are better off just creating 4 tables.
如果你真的想要一个包含基本数据的表,你可以按照建议使用content_type和公共字段创建一个表,然后为每个类型和其他不同字段创建4个单独的表,然后在你做一个内连接时选择该类型的字段。但我个人认为你最好只创建4个表。
#2
0
maybe think of using the "has a" relationship. For instance, an event "has a" "web item handle" attached to it, and a "web item handle" is a thing with description, created date, title, 'owner' etc...
也许想到使用“有一个”的关系。例如,一个事件“有一个”“web项目句柄”附加到它,“web项目句柄”是一个描述,创建日期,标题,“所有者”等...
#1
0
Unless they truly have identical data, I would use separate tables. Having one table with some fields only used by specific content types is really not very good database design.
除非他们真的拥有相同的数据,否则我会使用单独的表格。有一个表只有特定内容类型使用的某些字段,实际上并不是很好的数据库设计。
If you really want one table with the basic data, you could create one as you suggested with a content_type and the common fields, and then have 4 separate tables for each of the types with the other distinct fields, then do an inner join when you select the fields for that type. But personally I think you are better off just creating 4 tables.
如果你真的想要一个包含基本数据的表,你可以按照建议使用content_type和公共字段创建一个表,然后为每个类型和其他不同字段创建4个单独的表,然后在你做一个内连接时选择该类型的字段。但我个人认为你最好只创建4个表。
#2
0
maybe think of using the "has a" relationship. For instance, an event "has a" "web item handle" attached to it, and a "web item handle" is a thing with description, created date, title, 'owner' etc...
也许想到使用“有一个”的关系。例如,一个事件“有一个”“web项目句柄”附加到它,“web项目句柄”是一个描述,创建日期,标题,“所有者”等...