如何针对单个父表设计多个子表？

I'd be grateful if someone could advise me what the correct way is to approach this:

如果有人能告诉我采用这种方法的正确方法,我将不胜感激:

I have a parent table, with primary key master_id.

我有一个父表,主键为master_id。

I have five child tables associated with the master_id, in a 1-to-n relationship, that record semantically different types of data (and wouldn't lend themselves to abstraction)

我有五个与master_id关联的子表,在一对一的关系中,记录语义上不同类型的数据(并且不适合抽象)

The only common fields in each child table are master-id (foreign key), created_by (user_id), created_time (timestamp).

每个子表中唯一的公共字段是master-id(外键),created_by(user_id),created_time(timestamp)。

The aim is to publish each child row associated with a master id in a chronological order on a webpage (imagine a forum posts style display), with the PHP building each "post" (i.e. row) slightly differently depending on the child table (and hence fields of data available to it).

目的是在网页上按时间顺序发布与主id相关联的每个子行(想象一个论坛帖子样式显示),PHP构建每个“post”(即行)根据子表略有不同(和因此可用的数据领域)。

Am I right in thinking there's no easy way to query this regardless of the table structure (and that ordering would be best done in PHP)? Is there any advantage to vertically partitioning out the 3 common fields into a single table combined with a table field?

我是否正确地认为,无论表结构如何,都没有简单的方法来查询(并且最好在PHP中进行排序)?将3个公共字段垂直分区为单个表并结合表字段是否有任何优势?

2 个解决方案

#1

This is a really difficult problem to solve, one I've worked on myself. Unfortunately, I don't have a good answer. You could use UNIONs and various types of JOINs, but you're going to potentially heavily kill your database's performance. Plus, you run into issues like, say you want the most 30 recent chronological entries. You'd have to query every child table for 30 entries (possibly 150 entries in all) and sort through them in your code since you don't know which child table(s) have the most recent 30 entries. 150 rows just to pull 30, blech.

这是一个非常难以解决的问题,我自己也在研究过。不幸的是,我没有一个好的答案。您可以使用UNION和各种类型的JOIN,但是您可能会严重破坏数据库的性能。另外,你会遇到类似的问题,比如你想要最近的30个时间顺序条目。您必须在每个子表中查询30个条目(总共可能有150个条目),并在代码中对它们进行排序,因为您不知道哪个子表具有最近的30个条目。 150行只需拉30,发痒。

Honestly, the best way I've found to implement such a thing is to have a table, possibly your master table, have columns dedicated specifically to what you want to show from your child table. For example, have columns in your master table that are something like, master_id, created_by, created_time, and notification_text (if you're trying to implement something like Facebook's timeline, for example). You could set up triggers on the child tables so that when you insert data into one of them, it automatically populates the data in the master table as well. Then when you're displaying the timeline, you just query the master table without bothering with the child tables at all.

老实说,我发现实现这样一个事情的最好方法是有一个表,可能是你的主表,有专门用于你想要从子表中显示的内容的列。例如,在主表中包含类似master_id,created_by,created_time和notification_text的列(例如,如果您尝试实现类似Facebook的时间线的话)。您可以在子表上设置触发器,以便在将数据插入其中一个表时,它也会自动填充主表中的数据。然后,当您显示时间轴时,您只需查询主表而不必烦扰子表。

Your schema would look something like:

您的架构看起来像:

Table master
- master_id
- created_by
- created_time
- notification_text

Table child1
- id
- master_id
- data1 (used to generate notification_text in master)

Table child2
- id
- master_id
- data21 (collectively used to generate notification_text in master)
- data22 (collectively used to generate notification_text in master)
- data23 (collectively used to generate notification_text in master)

...

#2

What about you do a star model? It's commonly use to create datawarehouses however you could try to use something similar in your solution. If you are not familiar with the star model you can see more here.

你做明星模特怎么样?它通常用于创建数据仓库,但您可以尝试在解决方案中使用类似的东西。如果您不熟悉星形模型,可以在这里看到更多。

Hope it helps.

希望能帮助到你。

#1