relational database design

Eep__

OK, I'm TRYING to figure out this relational database crap but it's wrackin' mah bwain. I want to store multiple genres for a game and am having trouble coming up with the most efficient solution.

Do I, like I've been attempting, have to use a genreID "set" type in the "compare" table in order to assign multiple genres to a game--or I must list gameID twice, which would end up duplicating other data?:

table: compare
fields (data): id, gameID, genreID ("set" type)
sample data: 1, 1, (1,2)

table: genres
fields: genreID, genre
sample data: 1, action
2, adventure

Or do I list games with multiple genres in the genres table (which still duplicates gameIDs but takes up less space than in the compare table)?:

fields: genreID, gameID, action, adventure, role-playing, simulation, strategy
sample data: 1, 1, action
2, 1, adventure

Help!

matto

am I right?:

you want to have two tables: GENRE and GAME, where a GAME has 1 GENRE and 1 GENRE has "various" GAMES related?

so you need the GENRE table:
id (unique key = primary key)
name

and the GAME table which has a reference to a GENRE for each entry:
id (primary key)
name
id_genre (foreign key that references to the GENRE which has this value as its primary key)

this would be a 1:n relationship between GAME and GENRE.

of course you can name the fields differently.

now you can do the following in php/html:

write a "new game" inserting form.
have the user type in the game's name.
let the user select (e.g. in a pull-down menu) the genre of the game. this menu (a selectbox) is populated by the entries of your GENRE table.

when user submits, php formulates the sql INSERT statement from both inputs and executes it.

hope that helps.

[deleted]

"where a GAME has 1 GENRE"

No, one game has many genres and each genre belongs to many games.

that's a n:m relation and 'requries' a cross reference table.

We've been over this in another thread, but eep seems reluctant to accept that solution 🙂

ultraslacker

I've set up something like this in ltree for postgresql - it isn't portable though, so it wouldn't work for mysql.

LTREE doc

Eep__

No, one game has many genres and each genre belongs to many games.

that's a n:m relation and 'requries' a cross reference table.

We've been over this in another thread, but eep seems reluctant to accept that solution 🙂

Only because it's not the only cross-reference table that would be required using that method--I'd have LOTS of them considering the layout of the relations thus far.

I would like to only have ONE cross-reference table, "compare", in order to reduce duplicated data as much as possible.

[deleted]

"in order to reduce duplicated data as much as possible."

well, like I said in the other thread, the cross-reference tables don't duplicate data, the are data.

The only thing that's in a cross-reference table is the record-id's of the data in the other tables. So no data ever get's replicated at all in a cross-reference table.

Eep__

I just don't see why I can't put all foreign keys into a central cross-reference table as I have outlined in http://tnlc.com/eep/compare/relations.jpg

As soon as I start adding "sub-cross-reference" tables (as I started to do in http://tnlc.com/eep/compare/relations.gif ), things start to get confusing and data is duplicated. Yes, even duplicated foreign keys is still duplicated data--which is what I'm trying to eliminate.

[deleted]

"I just don't see why I can't put all foreign keys into a central cross-reference table as I have outlined in"

Because you can't. The entire world uses cross-reference tables. Do you think they do that just for fun? 🙂 Or maybe because that is the only 'right' way to do it?

"Yes, even duplicated foreign keys is still duplicated data--which is what I'm trying to eliminate."

That's the problem, the PK IS NOT DATA, it is a reference TO data, and it SHOULD BE DUPLICATED AS MANY TIMES AS YOU NEED TO.

That is the point!

You CANNOT prevent duplication of some data, simply because you reference some data more than once.

But you can minimize the duplication by not duplicating the data, but by duplicating the PK values that reference the data instead.

And by far the easiest and most flexible way of duplicating the PK values is by using a reference table.

And if that gives you twice as many tables: who cares? That's the way databases work, databases are designed to work with hundreds if not thousands of tables, they have complex grammatical structures to handle joined tables in the most interesting ways.

That's how things work, learn to live with it.

Eep__

Well, I will learn to live with it until I find a better way, which I believe there is using a SINGLE cross-reference table, but seeing as how I'm still new to this database stuff, I'll accept the way things are done...for now. 😉

Anyway, am I on the right track with "conventional" RDBMS design in http://tnlc.com/eep/compare/relations2.gif ? As you can see, so far, everything is linking back to the games table and there are numerous (ugh) cross-reference tables. The detached stuff on the bottom half has yet to be connected.

The "compare" table most likely won't be necessary with this method because it basically represents the end-result final compiled (queried) table anyway, right?

Any corrections/suggestions for how I should combine these tables?

Thanks for your help.

[deleted]

I suggest you first write down a list of exacly what you want to store in the database, and how the data relates to eachother

games:
enginetype, one per game ->engines
title, one per game
developer, one per game
genre, one ore more per game ->genres

genres:
name, one per genre

engines:
name, one per engine
developers, one or more ->developers
playertypes, one or more ->playertypes

playertypes:
name, one
description, one

etc etc.
This makes it a lot easier to work out the actual model because you can count the tables and table-references to see if you have added eveything.

Also, from a practical point-of-view: try to build the schema as a tree, with the most 'daddy' table at the top, and everything it refers to below it, so that iof table A refers to tables B,C and D, and B,C and D are under A in the schema. Otherwise you may get circular references and not notice it untill things go horribly wrong.

Eep__

But am I on the right track with what I have so far via that image?

If you want to see how everything relates, go to http://tnlc.com/eep/compare/gameform.html and also check out the comparison table at http://tnlc.com/eep/compare/table.html

The problem is, I want to provide lots of text-entry fields for people to enter in other things not listed in the available options. So how would I have the table set up for it to allow publishers to be added if the one in the text-entry field doesn't match any in the "publishers" table? This is how http://mobygames.com/ handles submissions and I'd like this functionality in my database as well. Do I have an "other" option in the "publishers" table or do I put the submitter-added publishers in a "holding"-type table for later validation (which I would like to do before a game is added anyway--I presume I can just have a "visible" entry in the "games" table for that, though).

With the amount of text-entry fields the form has I want the database to be as easily expandable as possible since there are so MANY options that can be added onto and the less people have to type the better. In essence, I want the database to learn as it gets data so the next submitter has an easier time entering data via checkboxes, pull-down lists, and radio buttons vs. text-entry fields.

Eep__

I still need help with this please...http://tnlc.com/eep/compare/relations3.gif for the latest relationship diagram.

Anyone??

[deleted]

"The problem is, I want to provide lots of text-entry fields for people to enter in other things not listed in the available options"

Then you can/must add an 'other' field for every relation.

So if you allow for people to enter freetekst if the genre does not exist, then you should add an 'othergenre' field next to the genreid in the cross-reference table.

Later when you 'approve' the entries, you can add the contents of the 'other' field into the genres table, and update the cross-reference table to store the new genreid in it and remove the value from the 'other' field.

Eep__

Why wouldn't the "other" field go in the "genres" table so it acts like the other genre entries and I don't have to duplicate even MORE data/keys than I already am with this cross-reference crap?

You never answered me if I'm even on the right track with my current relationship diagram. Am I?? 😕

[deleted]

"Why wouldn't the "other" field go in the "genres" table so it acts like the other genre entries and I don't have to duplicate even MORE data/keys than I already am with this cross-reference crap?"

Would you please give up on that 'dupication' crap? We already established that you somehow don't want to get the point on that issue.

The 'other' field is used when the required genre does not exist, and you don't want people to just add new genres when it suits them, you want to monitor the genres and put them in the genres table only when you think it's needed there. So obviously it doesn't belong in the genres table. (besides, do you have any idea of how complex the user-interface has to be to allow users to add new genres while they are entering data into the main form?)

Yes you are on the right track with the design, but you've got at least one mistake in the games-games_websites-websites relation, the games table already has a websiteid, which cannot be, because the link to websites is in the games_websites table.

Eep__

Good catch with the duplicate websiteIDs--thanks.

Are the "id" fields in the cross-reference tables even necessary since they're made up of 2 foreign keys? In the article here on PHPBuilder called "Database Normalization And Design Techniques", on page 5 it shows the "url_relations" table with a "relation_id" field yet in the query on the next page to get all users and URLs, "relation_id" isn't used so can't it be eliminated?

I ask because I'm noticing in my numerous cross-reference tables, their IDs aren't used at all but I'm wondering what they COULD be used for?

Thanks again for your help--I really appreciate it. I just want to make sure my database is designed well at the beginning so I don't have to rewrite it down the road.

[deleted]

Every table should have an ID column, simeply because that is the only sane way to reference a record.

You'll find that sometime you hardly ever use the ID, and other times you cannot live without it. The simple fact is that it's more difficult to add an ID column lateron than it is to just not use it.

The ID's in the cross-reference tables will come in handy later when you start doing conditional deletes etc, where you first want to see if there are relations, and later delete them. Then you can fetch tie ID's of the relations, store them in memory, and later do DELETE FROM crossreferencetable WHERE ID IN (...) which is much faster than re-searching the cross-reference table.

mattr

Every table should have an ID column, simeply because that is the only sane way to reference a record.

No, no, no, no, NO!

That goes against every tenet of relational theory.

In this case, the relationID is wholly unnecessary and SHOULD be removed from the example as it is misleading and WRONG.

[deleted]

Care to elaborate?

bharatgautam

Please give me details about this