sql - Relational Database Design (MySQL) -

i starting new project website based on "talents" - example:

• models
• actors
• singers
• dancers
• musicians

the way propose each of these talents have own table , include user_id field map record specific user.

any user signs on website can create profile 1 or more of these talents. talent can have sub-talents, example actor can tv actor or theatre actor or voiceover actor.

so example have user - model (catwalk model) , actor (tv actor, theatre actor, voiceover actor).

my questions are:

1. do need create separate tables store sub-talents of user?

2. how should perform lookups of top-level talents user? i.e. in user table should there fields id of each talent? or should perform lookup in each top-level talent table see if user_id exists in there?

3. anything else should aware of?

ok sorry incorrect answer.. different approach.

the way see it, user can have multiple occupations (actor, model, musician, etc.) think in objects first translate tables. in p.o.o. you'd have class user , subclasses actor, model, etc. each 1 of them have subclasses tvactor, voiceoveractor... in db you'd have table each talent , subtalent, of them share same primary key (the id of user) if user 4 , actor , model, have 1 registry on actor's table , on model table, both id=4

as can see, storing easy.. complicated part retrieve info. that's because databases dont have notion of inheritance (i think mysql has haven't tried it).. if want subclases of user 4, see 3 options:

• multiple selects each talent , subtalent table have, asking if id 4.

  select * actor id=4;select * tvactor id=4;

• make big query joining talent , subtalent table on left join

  select * user left join actor on user.id=actor.id left join tvactor on user.id=tvactor.id left join... user.id=4;

• create talents table in nxn relation user store reference of each talent , subtalents user has, wont have ask of tables. you'd have make query on talents table find out tables you'll need ask on second query.

each 1 of these 3 options have pros , cons.. maybe there's 1 =)

good luck

ps: ahh found option here or maybe it's second option improved