Game

Hi all,

Now I have implemented my normalised database with my php code it seems that denormalising would be more efficient :S.

I have 3 tables for my asteroid control.

A main one with information about the user. Like "username"(primary) and resources password etc.

Then there is a table containing asteroids. With "asteroidID"(primary), "type", "mass", "distance"

Then I have a table to assign ownership of asteroids to users. with just 2 fields both primary keys.
"username" and "asteroidID"

There are other tables that link to the "asteroidID" like the mining table indicated which is being mined.

When I come to delete the asteroid is added work with the username to asteroidID link table. Whats the advantage of doing it the way I have which is following normalisation rule over the easier solution which is to remove the link table and put the username field into the asteroid table.?

Thanks

re: Game

I don't quite understand the thought behind the table linking the user to the asteroid. Does each user get their own personal asteroid?

A linking table is used to provide the solution of a many-to-many relationship. Many users (own? inhabit? destroy?) many asteroids.

If you put the username in the asteroid table, then that asteroid can only be assigned to one user creating a one-to-one relationship. Each one asteroid belongs to one user.

re: Game

Each user has a variable length set of asteroids that are linked to them, that they scan for and on detection have the ability to select for mining.

Its a 1-many relationship im going for here but normalisation says username shouldn't be in the asteroids table as it doesn't relate to the primary key or something like that. It all gets a little confusing sometimes. random note: Asteroids are destroyed when they have been fully mined.

The options I see to create the one to many is to have the linking table as I currently have. Or to have the username in the asteroid table and have the username and asteroidID both as primary keys.

re: Game

Ah, okay. So 1 user has many asteroids. Then, yes, the user does go into the asteroid table. The user relates to the primary key in that the user is the owner of that particular asteroid.

You don't need the table linking them together since you don't have a many-to-many relationship. If each asteroid could be owned by more than one person, then you would need to link them to show ownership.

So, it sounds to me like you want something like this:

tblUser -> username (PK), password, resources, etc.
tblAsteroid -> asteroidID (PK), type, mass, distance, username (FK)

This way, you can delete the asteroid and along with it, the owner, but the username still exists in it's own table.

I hope this helps and good luck!

re: Game

Hi.

I would have though that would be a one-to-many (1:N) relationship; each user being able to own multiple asteroids.

But yea. If you only want each asteroid to be linked to a single user then you would create a one-to-many (1:N) relationship.
Meaning; putting the PK of the user table into the asteroid table as a foreign key.

If you want each asteroid to be linked to multiple users, then you would need a many-to-many (N:M) relationship, which would require a intermediary table (link table).


I would advise you to use an integer as a PK in your user table, rather then using the username. That way you would only have to put the integer into the asteroid table, rather then the username, which will both safe disk space and speed up your queries.
(String comparison is a lot more complex then integer comparison)

re: Game

Atli is right. Putting the username in the asteroid table as a foreign key is a one-to-many relationship. I mispoke in my first post. Hopefully my second post explained it better.

re: Game

This sounds like good advice thanks

So the username would be a foreign key not a composite? Was this choice made on the fact the asteroidID can create unique PK's on its own or that username is a primary key in the user table?

I have other tables where there is a nodeNum field that is 1,2,3,4 for each user so as this will repeat for every user I made it composite key with username. Reckon it would be better to have an ID in there to be PK? So then username would be FK, ID PK and nodeNum normal.

Thanks for the good input from both of you given me loads of modifications to make.