CODEWITHSUNDEEP
phpmysqlhtmlsql
Al Hennessey
Al Hennessey

Reputation: 2445

Worried about too many fields in table

i am currently planning out my next project which is a text based mmorpg game. I am currently trying to design certain parts of the database and have hit a bit of a problem that i have never had before. One part of the game allows the player to buy a car and add addons to it. I was going to have a different table altogether to manage the addons for the car, but a user could have up to 100 addons for a single car, which would require over 100 fields, of course i am not happy with this many fields in one table as it could become difficult to manage, is there any other way to split them up into multiple table?

Thanks

Upvotes: 0

Views: 172

Answers (4)

Jacob Mattison
Jacob Mattison

Reputation: 51062

Why does each addon have to be a separate column? Couldn't you have a many-to-many join table that would link car to addon?

Car
ID | Owner
1  | Jacob
2  |  Mary

Addon
ID | Name        | Price
1  | Flame decal | $10
2  | CD Changer  | $150

Car_Addon
Car_ID | Addon_Id
1      |  1
1      |  2
2      |  2

This indicates that Jacob's car has a flame decal and a cd changer, while Mary's car only has a cd changer.

Advantages of this approach:

  • You can use foreign key constraints to ensure that no invalid records can be created
  • It's easy to query in either direction -- which addons does this car have or which cars have a given addon
  • The meaning of the relation is clear -- you're not relying on decoding serialized data within a single field
  • You can store data about the association between car and addon -- the car_addon table can have a column for when the addon was added to that car, how it was paid for, whether it was part of a discount package, etc.

Upvotes: 9

Diego
Diego

Reputation: 36166

You should have a table for Cars (ID, Name) for example, one table for ADDON (ID, Name) too and another table to link these talbes called CAR_ADDON (idCar, idADDON).

That would be the best approach

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135868

You have a many-to-many relationship between cars and addons. You need an intermediary junction table to resolve that relationship.

enter image description here

Upvotes: 7

JohnFx
JohnFx

Reputation: 34907

No.

Split them into multiple tables. If you have 100+ fields in a table, 99.9% of the time you haven't normalized your design enough. A sure sign of a badly structured database is a lot of sparsely populated fields.

Why are you hesitant to split it?

Upvotes: 1

Related Questions