best way to store checkbox + text-entry fields

From: Eep² <eep_at_tnlcNOSPAM.com>
Date: Sun, 13 Oct 2002 03:06:10 GMT
Message-ID: <3DA8E330.77A22523_at_tnlcNOSPAM.com>

I'm designing a PHP-MySQL computer game comparison database and I'm still not sure how to store checkboxes and text-entry fields as I have them laid out in the game submission form at http://tnlc.com/eep/compare/gameform.html . I would VERY MUCH appreciate any help in how best to design the tables for this database. Here is what I've come up with so far:

1. a single cross-reference table that holds the IDs of most other tables in a heirarchial structure (see http://tnlc.com/eep/compare/relations.jpg)
2. multiple games_whatever (genre, publisher, developer, engine, etc) cross-reference tables (see http://tnlc.com/eep/compare/relations3.gif - disregard the duplicate websiteID fields)
3. same as 2 except adding the text-entry fields in the cross-reference tables (see http://tnlc.com/eep/compare/relations4.gif - only "genres" table has this)
4. same as 2 except the cross-reference tables consist of id, gameID, and each column of the table's contents instead of just listing the contents in a single column as in the previous examples) - for example:

table: games
fields: id, name
data:
1, Tomb Raider
2, Tomb Raider II
...

table: games_genres
fields: id, gameID, action, adventure, role-playing, simulation, strategy, other data:
1, 1, 1, 1, null, null, null, null
2, 2, 1, 1, null, null, null, null
...

This allows only adding the appropriate genres to each game without having to duplicate gameID keys in "normal" cross-reference tables:

table: games_genres
fields: id, gameID, genreID
data:

1, 1, 1
2, 1, 2
3, 2, 1
4, 2, 2

...

Is #4 the best, most efficient way to do this or must I use the wasteful duplicate-key design?

I want to allow game submitters to enter new fields into the various tables via text-entry fields (which I validate) so I need some way of storing them (and turning them into checkboxes) but I'm not sure how to design this either. Also, not all text-entry fields will be added as new checkboxes (particularly if the feature is VERY unique/specific to the game).

Thanks for any assistance! Received on Sun Oct 13 2002 - 05:06:10 CEST