Question about schema

Hi

I need to design the database that will be storing a lot of personal
information and preferences for my employer users. Right now I'm trying
to find the best way to design the database. But to the point.

Lets say one of the preferences is sport as an interest.
On the web page when you select sport you will be asked for your
favorite sports to select (baseball, hokey, basketball...) then you will
be able yo choose the favorite teams from and lastly your favorite
players within those teams or just sport personalities in general.

My idea for database is:

Create table with sports (sports_tbl) with all supported sports and spid
as PK.
Then table with teams (teams_tbl) with all teams and teamid as PK.
Next one will be sport_personality_tbl with sppid as PK.

Now we will link all the tables with link table:
spid, teamid, sppid all as FK.

This part will be for storing the data about sport. The r will be also
another table which will hold the user preferences. Right now I'm not
sure how it should look like. Do you?

The same thing i need to do with other areas of interests like politics,
health and so on.

Is there a better way to do that except the one withe one i described above?

Thank you
Ralph

Re: Question about schema

don't make it any harder than it already is:

sports table --> spid
teams --> teamid, (spid as FK) because every team is in a sport
sport_personality_table --> sppid, (teamid, spid as FK) because every
personality is within a team and a sport

I'd use innodb engine to enforce the relationships.

good luck


Ralph wrote:
> Hi
>
> I need to design the database that will be storing a lot of personal
> information and preferences for my employer users. Right now I'm trying
> to find the best way to design the database. But to the point.
>
> Lets say one of the preferences is sport as an interest.
> On the web page when you select sport you will be asked for your
> favorite sports to select (baseball, hokey, basketball...) then you will
> be able yo choose the favorite teams from and lastly your favorite
> players within those teams or just sport personalities in general.
>
> My idea for database is:
>
> Create table with sports (sports_tbl) with all supported sports and spid
> as PK.
> Then table with teams (teams_tbl) with all teams and teamid as PK.
> Next one will be sport_personality_tbl with sppid as PK.
>
> Now we will link all the tables with link table:
> spid, teamid, sppid all as FK.
>
> This part will be for storing the data about sport. The r will be also
> another table which will hold the user preferences. Right now I'm not
> sure how it should look like. Do you?
>
> The same thing i need to do with other areas of interests like politics,
> health and so on.
>
> Is there a better way to do that except the one withe one i described
> above?
>
> Thank you
> Ralph