Professional Web Applications Themes

Advice the database schema - MySQL

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 ...

  1. #1

    Default Advice the database 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
    Ralph Guest

  2. #2

    Default Re: Advice the database schema


    Ralph wrote: 

    I think you have just summed up the best way of doing things. You would
    probably need a few unique keys on thos tables to ensure you don't get
    any duplicate data. But I think that your database should be reletively
    free of redundant data. Doing it the way you described also means that
    it should be easier to expand and add new tables as you wish without
    doing too much work.

    Best of Luck.

    Daz.

    Daz Guest

  3. #3

    Default Re: Advice the database schema

    Daz wrote: 
    >
    > I think you have just summed up the best way of doing things. You would
    > probably need a few unique keys on thos tables to ensure you don't get
    > any duplicate data. But I think that your database should be reletively
    > free of redundant data. Doing it the way you described also means that
    > it should be easier to expand and add new tables as you wish without
    > doing too much work.
    >
    > Best of Luck.
    >
    > Daz.
    >[/ref]
    Thank you. But in the same time retrieving data from those tables will
    involve multi table queries all the time :(

    Ralph
    Ralph Guest

  4. #4

    Default Re: Advice the database schema

    Ralph wrote: 
    >>
    >>
    >> I think you have just summed up the best way of doing things. You would
    >> probably need a few unique keys on thos tables to ensure you don't get
    >> any duplicate data. But I think that your database should be reletively
    >> free of redundant data. Doing it the way you described also means that
    >> it should be easier to expand and add new tables as you wish without
    >> doing too much work.
    >>
    >> Best of Luck.
    >>
    >> Daz.
    >>[/ref]
    > Thank you. But in the same time retrieving data from those tables will
    > involve multi table queries all the time :(
    >
    > Ralph[/ref]

    Ralph,

    Retrieving data from multiple tables is what relational databases are
    all about. And if you have the proper indexes they aren't that much
    overhead. In fact, you can come up with designs where multiple tables
    can actually be faster to access than if everything were in one table.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. how to prep database schema
    By pallabi2k@gmail.com in forum MySQL
    Replies: 1
    Last Post: May 25th, 04:31 PM
  2. Configurable database schema
    By Brian G. in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 22nd, 06:01 PM
  3. Loading Database Schema
    By Scott*e in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 24th, 05:34 PM
  4. Looking for sample code and database schema
    By Jeff Cochran in forum ASP Database
    Replies: 3
    Last Post: February 23rd, 08:11 PM
  5. Replies: 3
    Last Post: July 11th, 04:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139