SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Database (Table) Stucture

    I'm creating a database (table) full of specification data for mobile devices. So far the table has 70 fields/columns which includes things like screen type, battery type etc. The table will hold around 600 devices.

    Now, I wish to include a rating system so that each device can be rated from 1 to 5 in 3 aspects (design, features, performance). With the size of table currently at 70 fields/columns, would it make any difference if I included the 15 extra fields (in this same spec table) required to support my rating system - or should I create a seperate votes tables?

    All I really want to know is, does it make any difference whether I have so many fields in a table? Will it slow things down/cause more load etc? Taking into account that this will be used on a very popular site, optimisation is important. Also, almost everything (including vote counts) will be extracted when this table is queried. And the only updatable part of the table when used live, are the voting fields.

    Your advice/thoughts would be appreciated.

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2003
    Location
    Mexico City
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, there are several approachs to this, but let me first comment on the performance issue.

    It is indeed a resource drag to have so many columns in a table, if only because of the time it takes for the information to travel from the DB server to your application. But as you say, if most/all of the fields will be used most of the time, it would seem there's no alternative.

    Maybe you could post your schema, so we can have a look at it and see if there are any specific optimization tips for your case.

    Anyway, so how to implement a voting feature? I recently did just that, and right now I can think of 2 ways of implementing it:

    * If you don't want your users change their votes, you could just calculate a device rating each time someone votes for it. However, this will only work for plain ratings, i.e., the users can only vote for it or against it, not from say, a scale from 1 to 5.

    * Have a separate table, say, users_devices, with fields for each user and the device they voted on, and either fields for each aspect you want to have ratings on, or even another table (i.e., rating_aspects).

    If you go the second route, I suggest you also cache the rating of each device, so you don't have to calculate it each time you retrieve it from the database.

    Heres what you may end up with:

    table users_devices:
    user_id
    device_id
    design INT <- store the rating the user_id gives to the device_id
    features INT
    performance INT

    table devices:
    design <- store the value you calculate from the users_devices table
    features
    performance

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply ivanv.

    After giving it some thought, it would probably just be easier to do add the following fields to the current devices table.

    vote_design INT
    vote_features INT
    vote_performance INT
    vote_count INT

    From this, I can easily calculate the averages for each - which is what I want, and it probably wouldn't need to be cached in another table.
    My initial idea was to store each rating in fields numbered from 1 to 5 for each aspect, which would mean adding 15 new fields, and is where I was getting a little worried with regard to resources. I've realised this isn't required.

    Thanks

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2003
    Location
    Mexico City
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps I'm not getting what you're trying to accomplish, otherwise, How are you going to calculate the average of each aspect if you don't store each vote's value?

    From your count column you could only calculate how many people have voted for each device, but not which rating they gave it. And even so, you'd need a count column for each aspect, wouldn't you? And if this is the case, you'd calculate the averages based on what, the cound and...?

    Anyway, if you've found what you're looking for, good, if not, feel free to ask again

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to confuse you, I'm almost confusing myself here.

    I don't want to count the number of votes given to each aspect.

    1 vote = 3 aspect scores from 1-5. The value of each score will be added to the corresponding aspect, but seen as 1 vote. The total scores of each aspect will then be divided into the total votes to get the average score of each aspect. Again, the aspect scores are not seen as individual votes - and also, my visitors will be required to vote 1 or above for each aspect so the average will always be correct.

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2003
    Location
    Mexico City
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh right, I see what you mean. Good approach.

    Wouldn't you still want to cache the average, say in a vote_average column? This way when your site grows you don't have to worry that your app or database is doing too many calculations (i.e., for 1000 visits, 1000 calculations, which will render the exact same result unless someone votes) with ever increasing values. Just an opinion.

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good idea, I will cache the average.

    Cheers


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
  •