关系数据库设计（MySQL） [英] Relational Database Design (MySQL)

问题描述

我正在开始一个基于天赋的网站的新项目，例如：

• 模型


• 演员


• 歌手


• 舞者


• 音乐家

我建议这样做的方式是，每个人才都有自己的表，并且包含一个user_id字段来将记录映射到特定的用户。 >

任何在网站上注册的用户都可以创建一个或多个这些人才的配置文件。一个人才可以有次要的人才，例如一个演员可以是电视演员，戏剧演员或配音演员。

所以例如我有用户A - 他是一个模特（时装表演模特）和一个演员（电视演员，剧院演员，Voiceover演员）。

我的问题是：

1. 我需要创建单独的表来存储此用户的子人才？




2. 我应该执行这个用户的顶级人才查找吗？即在用户表中应该有哪些字段为每个人才的ID？或者我应该在每个顶级人才表中执行查找以查看是否存在user_id？




3. 我还应该注意什么？ p>

解决方案

对不正确的答案抱歉，这是一个不同的方法。

我看到的方式，用户可以拥有多个职业（演员，模特儿，音乐家等）通常我所做的是先将对象先思考，然后将其翻译成表。在P.O.O.你会有一个类User和子类Actor，Model等，每个人也可以有一个子类，如TvActor，VoiceOverActor ...在一个数据库中，你将有一个表格为每个人才和subtalent，他们都共享相同的主键（用户的id），所以如果用户4是Actor和Model，你将在Actor表上有一个注册表，在Model Table上有一个注册表，其中id = 4

正如你所看到的，存储很容易。复杂的部分是检索信息。这是因为数据库没有继承的概念（我认为mysql有但没有尝试过）..所以如果你想现在的用户4的子域，我看到三个选项：

• 您拥有的每个人才和辅助表的多个SELECT，询问他们的身份是否为4。

  
  
  

  SELECT * FROM Actor WHERE id = 4; SELECT * FROM TvActor WHERE id = 4;




• 查询左侧的所有人才和附表加入

  
  
  

  SELECT * from User LEFT JOIN Actor ON User.id = Actor.id LEFT JOIN TvActor ON User.id = TvActor.id LEFT JOIN ... WHERE User。 id = 4;




• 创建一个与用户NxN关系的Talents表，以存储每个人才和小计的参考用户有，所以你不必要求所有的表。您必须在才华表上查询，才能找到在第二个查询中需要查询的表。

每一个这三个选项有其利弊。也许还有另一个=）

祝你好运

PS ：啊，我发现另一个选项 here 或者也许只是第二个选项改进了

I am starting a new Project for a website based on "Talents" - for example:

• Models
• Actors
• Singers
• Dancers
• Musicians

The way I propose to do this is that each of these talents will have its own table and include a user_id field to map the record to a specific user.

Any user who signs up on the website can create a profile for one or more of these talents. A talent can have sub-talents, for example an actor can be a tv actor or a theatre actor or a voiceover actor.

So for example I have User A - he is a Model (Catwalk Model) and an Actor (TV actor, Theatre actor, Voiceover actor).

My questions are:

1. Do I need to create separate tables to store sub-talents of this user?

2. How should I perform the lookups of the top-level talents for this user? I.e. in the user table should there be fields for the ID of each talent? Or should I perform a lookup in each top-level talent table to see if that user_id exists in there?

3. Anything else I should be aware of?

解决方案

ok sorry for the incorrect answer.. this is a different approach.

The way i see it, a user can have multiple occupations (Actor, Model, Musician, etc.) Usually what i do is think in objects first then translate it into tables. In P.O.O. you'd have a class User and subclasses Actor, Model, etc. each one of them could also have subclasses like TvActor, VoiceOverActor... in a DB you'd have a table for each talent and subtalent, all of them share the same primary key (the id of the user) so if the user 4 is and Actor and a Model, you would have one registry on the Actor's Table and another on the Model Table, both with id=4

As you can see, storing is easy.. the complicated part is to retrieve the info. That's because databases dont have the notion of inheritance (i think mysql has but i haven't tried it).. so if you want to now the subclases of the user 4, i see three options:

• multiple SELECTs for each talent and subtalent table that you have, asking if their id is 4.

  SELECT * FROM Actor WHERE id=4;SELECT * FROM TvActor WHERE id=4;

• Make a big query joining all talent and subtalent table on a left join

  SELECT * from User LEFT JOIN Actor ON User.id=Actor.id LEFT JOIN TvActor ON User.id=TvActor.id LEFT JOIN... WHERE User.id=4;

• create a Talents table in a NxN relation with User to store a reference of each talent and subtalents that the User has, so you wont have to ask all of the tables. You'd have to make a query on the Talents table to find out what tables you'll need to ask on a second query.

Each one of these three options have their pros and cons.. maybe there's another one =)

Good Luck

PS: ahh i found another option here or maybe it's just the second option improved

这篇关于关系数据库设计（MySQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！