Relational Database Design (MySQL)

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?

---

before answering your questions... i think that user_id should not be in the Talents table... the main idea here is that "for 1 talent you have many users, and for one user you have multiple talent".. so the relation should be NxN, you'll need an intermediary table

see: many to many

now

Do I need to create seperate tables to store sub-talents of this user?

if you want to do something dynamic (add or remove subtalents) you can use a recursive relationship. That is a table that is related to itself

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

see : recursive associations

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?

if you're using the model before, it could be a nightmare to make queries, because your table Talents is now a TREE that can contain multiple levels.. you might want to restrict yourself to a certain number of levels that you want in your Talent's table i guess two is enough.. that way your queries will be easier

Anything else I should be aware of?

when using recursive relations... the foreign key should alow nulls because the top levels talents wont have a parent_id...

Good luck! :)

EDIT: ok.. i've created the model.. to explain it better

Edit Second model (in the shape of a Christmas tree =D ) Note that the relation between Model & Talent and Actor & Talent is a 1x1 relation, there are different ways to do that (the same link on the comments)

to find if user has talents.. join the three tables on the query =) hope this helps

---

You should have one table that has everything about the user (name, dob, any other information about the user). You should have one table that has everything about talents (id, talentName, TopLevelTalentID (to store the "sub" talents put a reference to the "Parent" talent)). You should have a third table for the many to many relationship between users and talents: UserTalents which stores the UserID and the TalentID.

Here's an article that explains how to get to 3rd NF:

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

---

This is a good question to show some of the differences and similarities between object oriented thinking and relational modelling.

First of all there are no strict rules regarding creating the tables, it depends on the problem space you are trying to model (however, having a field for each of the tables is not necessary at all and constitutes a design fault - mainly because it is inflexible and hard to query). For example perfectly acceptable design in this case is to have tables

Names (Name, Email, Bio)

Talents (TalentType references TalentTypes, Email references Names)

TalentTypes (TalentType, Description, Parent references TalentTypes)

The above design would allow you to have hierarchical TalentTypes and also to keep track which names have which talents, you would have a single table from which you could get all names (to avoid registering duplicates), you have a single table from which you could get a list of talents and you can add new talent types and/or subtypes easily.

If you really need to store some special fileds on each of the talent types you can still add these as tables that reference general talents table. As an illustration

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

Do notice that this is only an illustration, it might be sensible to have Salary in the Talents table and not to have tables for specific talents.

If you do end up with tables for specific talents in a sense you can look at Talents table as sort of a class from which a particular talent or sub-talent inherits properties.

---

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