I'm now creating my database to have an application according to "Talents", for instance:
- Ballroom dancers
- Music artists
I began an identical question yesterday and also have made some progress in the reactions however i thought it might be simpler to begin a brand new question now. Making this things i have right now:
Table talent: - id - name Table subtalent: - id - name - talent_id (FK) Table user: - id - name
Any user who subscribes online can produce a profile for a number of of those talents. A talent might have sub-talents, for instance an actress could be a tv actor or perhaps a theatre actor or perhaps a voice-over actor.
Now I have to know solutions towards the following:
1) How must i store what talents and sub-talents a person has. I suppose there will have to be two more tables (talent_user and subtalent_user)?
2) The consumer can specify specific details about each TOP LEVEL talent they've. The fields will be different for every talent and so i assume this can require another group of tables? And just how would these be from the user record, i.e. an amount the foreign key be?
Can you ever must have a sub-talent of the sub-talent? Ie. There might be conditions where there are many different amounts of talents, in which the parent talent has sub talents, however the parent is really a sub talent to a different talent. Wow, thats wordy! :)
Within this situation you would employ a self referencing table. Like below
Table:Talents - TalentID - ParentTalentID (References TalentID column, NULL if this Talent is a top level) - Name
You can then have as numerous groups, sub groups as you desire.
You would require a joining table like a User might have many talents along with a talent can fit in with many customers.
Table:UserTalents - TalentID (Fk) - UserID (Fk) Table:Users - UserID (Pk) - Name
Scrap the Subtalent. Talent is really a hierarchical talent - (ParentTalent area). Enables arbitrary depths.
You'll need a UserTalent table connecting a person to records int he talent table.
1) I'd agree.
Table talent_user: - id - user_id - talent_id Table subtalent_user: - id - user_id - subtalent_id
2) I believe the response to this is dependent on the couple of factors. Knowing just what the variations could be for various talents, I'd keep things simple (as with "don't believe way too hardInch) and make up a table for every different kind of form you will have. Otherwise, you might want to have only one table for those forms and save the shape data as, say, XML. This way you can preserve the dwelling from the original form without getting too complicated a database schema.
2) Maybe use a talent_feature table. To ensure that for every talent kinds the characteristics it has.
Feature = (id, name, description) talent_feature = (id, talent_id, feature_id)
Then you'll have a User_talent_feature for example:
user_talent_feature = (user_id, talent_feature_id, possible_further_fields)
This provides lots of versatility when it comes to adding additional features to some talent.
1) Either you may make every talent possess a subtalent to ensure that the connection will be towards the subtalent. So you won't ever possess a person being just an actress but instead a tv_actor, or broadway_actor etc. Then you'll need only the user_subtalent relation. You could discover the top level talent out of this. Or make use of the hierarchical structure suggested by other solutions.