Many-to-many that has fields with multiple values, setup? 1

[Loki13013]

Hey guys, thanks for all the help (past, present, and future).

I'm a total access newbie and would prefer to stick to the drag-and-drop simplicity if possible, though I'm familiar enough with programming and SQL to dip into the code if necessary.

I'm setting up a database to create relationships between two groups - Game Developers and Game Publishers. Each group has the same five fields to describe their game (for Developers) or the games they desire (for Publishers). These fields are:

Genre- Action, Adventure, Arcade, Board, Card, Casino, Classic, Handheld, Pinball, Puzzle, Rhythm and Dance, RPG, Simulation, Sports, Strategy

Platform- Playstation 2, Xbox,GameCube, PC Games, Game Boy, Wireless, N-Gage, Playstation, N64, Dreamcast, Mac, PC

Stage (of development)-Alphas, Betas, Concept, Pickup

Territory - N. America, Europe, Asia

Budgets - AAA, Mid-Prices, Value, < $10

So it seems that I have a many-to-many relationship, where a single game can be desirable to many publishers, and a single publisher may be in the market for many of the games in my database. So this approach occurred to me:

Create three tables, a table for all publishers, a table for all games, and a table that relates the common fields. The problem I see is that a given publisher may have multiple values for each of these fields, ie, a publisher may desire any game in and or all genres, any or all stages of production, any or all budgets, etc. And even though a given game should only be in a single stage of production and a single budget, it can be available for multiple platforms, multiple territories, and may possibly cross genres (an Action/Adventure game, or a game that combines RTS with RPG elements).

This problem of handling multiplie field values for a given record is the main problem. I'm thinking that instead of having the five fields, as mentioned above, and trying to fill some of the fields with multiple values, I'm going to have 38 fields, one for every possible value of all the fields, and simply mark it with a 1 or 0 to indicate whether a given record has that property or not. Seems very inelegant, but makes it easy to setup. And this is a relatively small database so I'm not too concerned with search times and such.

Any tips on this approach would be much appreciated, and I'd love to hear about a more elegant way to solve the same problem.

thx,

Brian

 

[Steve101]

You need to perform further data analysis on the above requirement.

(a) From the description, there are in fact a lot more tables than the three you mention. For example, erach of the five field that you mention above, contain 'reference' values which themselves should be represented and located in their own reference tables; eg. you could have a table called tblTerritory, which would contain a record for each territory of interest to you. This table would then be used in your user interface to allow selection of a territory. It would also be a simple matter to add another territory.

(b) Find yourself a book, or other material on data normalisation / data modelling. This will teach you how to convert a requirement into a conceptual database design and then into a physical database design.

(c) Some of the tables that I think you need to resolve the main many-to-many situation you mention above are as follows:
[tt]
tblDeveloper \
/ --->> tblPartyGame <<--------- tblGame
/
tblPublisher/
[/tt]

its kindof hard to represent the diagram properly here, but the table tblPartyGame is the intersecting table which effectively implements the many-to-many relationship between games and developers, and games and publishers.

(d) I've called Games and Developers collectively 'Parties', hense the name PartyGame. Since these seem to have a lot of similar characteristics, you could collapse these two tables into a single table, then use a PartyType field to distinguish the two ... just food for thought.

(e) The additional tables discussed in (a) would go to the left of the tblDeveloper and tblPublisher tables, an link into each of them in respective 1-m relationships.

This is a quick and dirty analysis, but hope it helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)