Relational Database Architecture (MySQL)

Question

I am launching a new project for a website based on Talents - for example:

• Models
• Actors
• Singers
• Dancers
• Musicians

As I propose to do this, 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 subscribes to the site can create a profile for one or more of these talents. A talent can have sub-talents, for example, an actor can be a television actor or a theater actor or an actor with voice recordings.

So, for example, I have user A - he is a model (Catwalk model) and an actor (television actor, theater actor, Voiceover actor).

My questions:

• Do I need to create separate tables to store this user's sub-talents?

• ? ? , , user_id?

• - , ?

Answer 1

. .

, (Actor, Model, Musician ..). , , , . P.O.O. User Actor, Model .., , TvActor, VoiceOverActor... , ( ), , 4 Actor Model, Actor, , id = 4

, .. . , ( , mysql , ). , 4, :

• SELECT , , , 4.

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

• ,

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

• Talents NxN , , . Talents, , .

.. , =)

PS: ahh , ,

Answer 2

... , user_id Talents... , " , ". NxN, -

.

- ?

- ( ), . , .

TABLE TALENT
-------------
id  PK
label
parent_id PK FK (a foreign key to table Talent)

.

? ? , user_id ?

, , , TREE, . , , , ..

- , ?

... null, parent_id...

!:)

: .. ..,

( = D). , , 1x1, ( )

, . =) ,

Answer 3

, (, dob, ). , (id, talentName, TopLevelTalentID ( "" "" )). : UserTalents, UserID TalentID.

, , NF:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Answer 4

, - .

, , , ( - ). ,

Names (Name, Email, Bio)

Talents (TalentType references TalentTypes, Email references Names)

TalentTypes (TalentType, Description, Parent references TalentTypes)

TalentTypes, , , , ( ), , / .

If you really need to save some special files on each type of talent, you can still add them as tables that reference the general talent table. As an illustration

Models (Email references Talents, ModelingSalary) -- with a check constraint that talents contain a record with modelling talent type

Note that this is just an illustration; it may be wise to have a salary in the talent table and not have tables for specific talents.

If you get tables for specific talents, in a sense, you can look at the Talent table as a class from which a certain talent or sub-talent inherits properties.