Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,559
    Thanks
    76
    Thanked 105 Times in 104 Posts

    How many is too many fields?

    If I had 500 fields in a table of my database, would that lag and affect anything rather than having say 75 fields?

    Because the user field is the main table and has roughly 250 fields in it, and I'd like to add all items to it, so that I won't have to merge tables to calculate.

  2. Users who have thanked myfayt for this post:

    applebao (06-25-2011)

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Well I'll defer to the experts on the performance issues with that many fields but I can tell you that from a usability and maintenance point of view that is too many fields in one table. Why do you have so many? Typically the reason a table has that many is people create fields like somechoice_1, somechoice_2, somechoice_3 instead of breaking that out into another table with a one to many relationship.
    OracleGuy

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by myfayt View Post
    Because the user field is the main table and has roughly 250 fields in it, and I'd like to add all items to it, so that I won't have to merge tables to calculate.
    Very bad idea!! Please read up on NORMALISATION and REFERENTIAL INTEGRITY.

    Storing all in one table, will make indexing for certain queries more difficult not to mention that management of relationships will be pretty much impossible. And the amount of duplicated data will be more inefficient than joining tables.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,559
    Thanks
    76
    Thanked 105 Times in 104 Posts
    Yeah I thought it was a bad idea. I don't know the knowledge or experience to do normalizing. I have lots of items so I was going to do it like this.

    USER TABLE
    drink (0)
    food (0)
    clothes (0)
    wood (0)

    etc. Those aren't the items but it's an example, so when they purchase something on the game it adds to that fields.
    I thought it'd be a nightmare to do it by type, like if type1 is healing, then all healing would go under there.

    I want to list everything they have in an inventory.

    So like

    Blue Herb: 15
    Red Herb: 123
    Yellow Herb: 9
    Orange Herb: 1,038

    etc.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Code:
    TABLE: Users
    userid INT PRIMARY KEY
    username VARCHAR
    ...other fields that describe the *USER* ...
    
    TABLE: Objects
    objectType INT AUTO_INCREMENT PRIMARY KEY
    objectName VARCHAR
    objectProperties ???
    
    TABLE: UserObjects
    userid INT REFERENCES Users(userid)
    objectType INT REFERENCES Object(objectType)
    quantity INT
    strength INT  (if appropriate)
    Or something along those lines.

    It's called a "many-to-many" table. Very very standard in normalized databases.

  • #6
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,559
    Thanks
    76
    Thanked 105 Times in 104 Posts
    Hmm I don't quite understand the UserObjects table. Are you merging the two together?

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Example of data in all tables:
    Code:
    Users
    1 : Homer
    2 : Marge
    
    Objects:
    1 : BlueHerb : Healing
    2 : OrangeHerb : Healing
    
    UserObjects
    1 : 1 : 15  (Homer has 15 Healing BlueHerbs)
    1 : 2 : 1038 (Homer has 1038 Healing OrangeHerbs)
    2 : 2 : 771 (Marge has 771 Healing OrangeHerbs)
    I'd suggest that the third column in Objects could be an ENUM column *OR* it could be an INT that is a foreign key to a separate ObjectTypes table.

  • #8
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Quote Originally Posted by myfayt View Post
    If I had 500 fields in a table of my database, would that lag and affect anything rather than having say 75 fields?

    Because the user field is the main table and has roughly 250 fields in it, and I'd like to add all items to it, so that I won't have to merge tables to calculate.
    500 fields in a table doesn't sound like a good idea to me either. With that many fields it looks like you're going to need a few tables.

    I think you should first read up on database normalization which should help you organise the data you want to store in the database.

    An extract from the above link:

    What is Normalization?

    Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
    Then draw up an ERD (Entity Relationship Diagram) which is a schematic representation of your database.

    Then build the sql scripts to create the database and the tables within it.
    Last edited by bullant; 06-25-2011 at 05:08 AM.

  • #9
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,559
    Thanks
    76
    Thanked 105 Times in 104 Posts
    Quote Originally Posted by Old Pedant View Post
    Example of data in all tables:
    Code:
    Users
    1 : Homer
    2 : Marge
    
    Objects:
    1 : BlueHerb : Healing
    2 : OrangeHerb : Healing
    
    UserObjects
    1 : 1 : 15  (Homer has 15 Healing BlueHerbs)
    1 : 2 : 1038 (Homer has 1038 Healing OrangeHerbs)
    2 : 2 : 771 (Marge has 771 Healing OrangeHerbs)
    I'd suggest that the third column in Objects could be an ENUM column *OR* it could be an INT that is a foreign key to a separate ObjectTypes table.
    Ah I think I am getting it. So you're comparing two like values. Like say you have 'playerid' you put that in Users and Objects table, both would have the same value. Then in the UserObjects table, you'd find a match on playerid, and then load the fields for that player. Am I getting closer?


  •  

    Posting Permissions

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