您的位置: 首页  >  技术问答  >  什么更好？ 具有相同实体的多个表与具有更多记录的少数关系表

什么更好？ 具有相同实体的多个表与具有更多记录的少数关系表

分类: 技术问答 • 2022-03-15 16:11:10

什么更好？ 具有相同实体的多个表与具有更多记录的少数关系表

问题描述：

I'm creating a database on mysql for a small app. Problem is there are too many fields that are identical on different Tables like

Table 1: Muncipal Issues: ID, UserID, Title, Location, Description, ImageURL,

Table 2: Harrasement Issues: ID , UserID, Title, Location, Description, ImageURL

Tables 3 same as above

both tables have almost same coulmns. i want to ask if it's better to use a relations and create a table for handling IDs and link it with other details or it's better to create a single table with an extra coulmn for these issues.

on one hand there'll be too many tables with identical columns. on the other hand there'll few tables with too many rows in it.

What will be best for performance more rows or more tables.

i'm using Mysql.

我正在为一个小应用程序在mysql上创建一个数据库。 问题是有太多相同的字段 在不同的表上，如 p>

表1：Muncipal问题： ID， UserID， 标题， 位置， 描述， ImageURL， p>

表2：Harrasement问题： ID， UserID， 标题， 位置， 描述， ImageURL p>

表3与上述相同 p>

两个表都有几乎相同的coulmns。 i想问一下是否更好地使用关系并创建一个表来处理ID并将其与其他细节链接起来，或者最好创建一个表格，并为这些问题添加额外的库存。 p>

一方面会有太多具有相同列的表。 另一方面，很少有表中包含太多行。 p>

什么最适合性能更多行或更多表。 p>

我正在使用Mysql。 p> div>

答

Firstly, unless you expect millions of records don't care that much about performance but care more about the structure of your data and how easy it will be to access it. Literally write down a list of data that you plan to extract in your app e.g. "find all issues today", "find all unresolved issues older than 6 months" and then try to build real SQL queries on your expected structure. If they're going hard try to change the structure.

To answer your question: it depends. The current structure has following benefits:

  • It's easy to query certain type of issues
  • It's easy to build a PHP application - just make one template form (or model) and then copypaste it with slight changes for other tables
  • In case of performance problems it may be easier to create a cluster by simply putting each table on the different db server.

and following downsides:

  • It's inflexible. Adding new field that you forgot to add in the beginning will be painful since you'll have to change 3 (or more) tables and then the same amount of pieces in your app.
  • Adding new types of issues will be painful and require creating new table.
  • Creating SQL-s for getting data like "all non-resolved issues (regardless of type)" will require complicated UNION-s. Moreover this UNIONS will require creating virtual field with issue type otherwise you can't tell from which table did certain id come.

The classical db approach recommends using one table for common fields and create derived tables for fields that are different. So:

  • issues table should have all common fields and is identified by PK issue_id
  • municipal_issues uses the foreign key to issues.issue_id and has only the specific fields
  • harassment_issues uses the foreign key to issues.issue_id and has only the specific fields
  • also the issues table has the issue_type field that takes values "harassment", "municipal" etc and helps finding the table where the additional data are stored.

This pattern is called "Class Table inheritance" and you may check out the SQL antipatterns presentation for more info and other approaches. This solves the flexibility issue and still allows re-creating each of the original tables with only one simple JOIN that goes pretty fast.

Also as a side note you may look into the db schema of bug-trackers like Mantis since this looks like the same domain.

相关推荐