Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: Database structure question

    Hi
    I have a table which stores high scores for a video game.

    scores table:
    | userid | level1 | level2 | level 3 | etc. |
    | 1 | 145 | 845 | 73 | etc. |
    | 2 | 150 | 798 | 75 | etc. |
    | 3 | 146 | 852 | 73 | etc. |

    Each of the levels have settings that I need to store. For example the name of the level, how it should be sorted, what format it should be in, and more.

    I have tried various methods to store this:
    - Have a level table, where the field name for the levels in the score table is the key and then I have | name | sort | format | etc.
    - By using a multidimensional array included in all the files.

    Both of these ways have their shortcomings, and so I thought, why not just add the settings to the scores table, with a negative user id like this:

    | userid | level1 | level2 | level 3 | etc. |
    | 1 | 145 | 845 | 73 | etc. |
    | 2 | 150 | 798 | 75 | etc. |
    | 3 | 146 | 852 | 73 | etc. |
    | -1 | name | name | name | etc. |
    | -2 | ASC | DESC | ASC | etc. |

    and so on. My problem is that all the level columns have been set to smallint to make the table faster. This is an obvious problem when I want to add text to the same column.

    What can I do about this? How much does it matter that a column is in the smallint format instead of varchar? There's about 150 levels and 200 users. Is there some way to append a table to another table, ie. to put a table at the bottom of another, so that I can have separate column settings? I'm sorry if this sounds awefully newbie-ish, that's because I am one

    I'd appreciate any help, thanks

    Fro

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a separate table, where the field name for the levels in the score table is the key and then you have | name | sort | format | etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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