1

I am now designing my database for an application based on "Talents", for example:

  • Models
  • Actors
  • Singers
  • Dancers
  • Musicians

I started a similar question yesterday and have made some progress from the responses but I thought it would be easier to start a new question now. So this is what I have at the moment:

Table talent:

- id
- name

Table subtalent:

- id
- name
- talent_id (FK)

Table user:

- id
- name

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.

Now I need to know answers to the following:

1) How should I store what talents and sub-talents a user has. I assume there will need to be two more tables (talent_user and subtalent_user)?

2) The user can specify specific information about each TOP LEVEL talent they have. The fields will vary for each talent so I assume this will require another set of tables? And how would these be linked to the user record, i.e. what would the foreign key be?

MAX POWER
  • 5,213
  • 15
  • 89
  • 141

4 Answers4

2

Would you ever need to have a sub-talent of a sub-talent? Ie. There could be circumstances where you have many different levels of talents, where the parent talent has sub talents, but the parent is a sub talent to another talent. Wow, thats wordy! :)

In this case you would use a self referencing table. Like below

Table:Talents

 - TalentID
 - ParentTalentID (References TalentID column, NULL if this Talent is a top level)
 - Name

You could then have as many categories, sub categories as you like.

You would then need a joining table as a User can have many talents and a talent can belong to many users.

Table:UserTalents

 - TalentID (Fk)
 - UserID (Fk)

Table:Users

 - UserID (Pk)
 - Name
Matt
  • 2,730
  • 4
  • 28
  • 35
1

  • Scrap the Subtalent. Talent is a hierarchical talent - (ParentTalent field). Allows arbitrary depths.

  • You need a UserTalent table linking a user to entries int he talent table.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • +1 that's what i told him yesterday =D http://stackoverflow.com/questions/4325161/relational-database-design-mysql/4325514#4325514 – pleasedontbelong Dec 02 '10 at 13:54
  • 2
    Hierarchical properties aren't well handled by relational databases: getting closures is hard. If you know in advance the number of levels, use that directly in your database. – Colin Fine Dec 02 '10 at 13:55
  • So if I have a single Talent table which stores top level talents and sub talents then how do I determine which is which? I.e. does there need to be a "type" field or for example if I want to retreive just the top level talents do I search for records where parent_id = null? – MAX POWER Dec 02 '10 at 14:06
  • Having worked with hierarchical data quite a lot in the past, I agree with Colin. Whether relational databases handle hierarchical data well or not, you should go with the simplest solution possible, especially if you're not terribly experienced, and hard-coding two levels of talent is the simplest solution possible. – Jason Swett Dec 02 '10 at 14:07
  • Jason/Colin - in that case do you have any suggestions for my first question? – MAX POWER Dec 02 '10 at 14:12
  • @Colin Fine, especially in mysql, still http://dev.mysql.com/tech-resources/articles/hierarchical-data.html – Unreason Dec 02 '10 at 14:37
  • in this case is not that hard to find the top level talents... in your query you just need to add `... AND parent_id IS NULL` (or whatever your column is called) cause top level talents dont have parents.. and since you only use two levels, to get the subtalents you'll just need to add `... AND parent_id IS NOT NULL` and that's it.. to me it seems easier than using two tables and relating users to both tables – pleasedontbelong Dec 02 '10 at 14:50
  • @GSTAR I submitted an answer - see that. – Jason Swett Dec 02 '10 at 14:51
  • Some databases have the ability to deal with hierarchical data quite cleanly - PostgreSQL, Firebird, SQL Server, Oracle, i think DB2. MySQL, though, is not one of them. – Tom Anderson Dec 02 '10 at 15:46
1

1) I would agree.

Table talent_user:
- id
- user_id
- talent_id


Table subtalent_user:
- id
- user_id
- subtalent_id

2) I think the answer to this depends on a few factors. If you know exactly what the different forms would be for different talents, I would keep things simple (as in "don't think too hard") and create a table for each different type of form you'll have. Otherwise, you may want to have just one table for all forms and save the form data as, say, XML. That way you could preserve the structure of the original form without having too complicated a database schema.

Jason Swett
  • 43,526
  • 67
  • 220
  • 351
0

2) Maybe you can use a talent_feature table. So that for each talent you can list the features that it has.

 Feature = (id, name, description)
 talent_feature = (id, talent_id, feature_id)

Then you will have a User_talent_feature such as:

 user_talent_feature = (user_id, talent_feature_id, possible_further_fields)

This gives a lot of flexibility in terms of adding new features to a talent.

1) Either you can make every talent have a subtalent so that the relationship will always be to the subtalent. So you will never have a person being just an Actor but rather a tv_actor, or broadway_actor etc. Then you will need just the user_subtalent relation. You can always find the top level talent from this. OR you can use the hierarchical structure proposed by other answers.

Vincent Ramdhanie
  • 102,349
  • 23
  • 137
  • 192