Urgent Ques. in databse design!

[tarequire]

hi all , i'll talk directly:

i have for ex. 4 tables , the main table is "Player" .
Suppose the following :
- every player has one or more weapons.
- every player has one or more mask (there is many typs of masks).
- every player has only 1 car, and 1 car takes 1 or more player.

---------------------
PLAYER
---------------------
- PlayerId ( int PK )
-name
-WeaponId
-MaskId
-CarId
- any other columns
---------------------

the weapon table

---------------------
WEAPON
---------------------
- WeaponId ( int PK )
-PlayerId
-weaponType
- any other columns
---------------------

the mask table

---------------------
MASK
---------------------
- MaskId ( int PK )
-PlayerId
-maskType
- any other columns
---------------------

the car table :

---------------------
CAR
---------------------
- CarId ( int PK )
-Cartype
- any other columns
---------------------

** the relations maybe as :

PLAYER (1)----->(many) WEAPON
PLAYER (1)----->(many) MASK
CAR (1)----->(many) PLAYER

** my ques. is : how can the foreign keys be done ? what it will be exactly ?

2nd : when and how i make more than one PK in one table ?

thx in advance...

Tareq

[TheCPUWizard]

1) The FK in a secondary table contains the PK of the referring table.

2) The P in primary key is there for a reasons. There is (at most) ONE primary Key.

[tarequire]

but i think there is something called : composite PK ?!!

[TheCPUWizard]

but i think there is something called : composite PK ?!!

That is a SINGLE key that is made up of multiple fields (for conditions where a single firld is not sufficient.

For example consider an invoicing system with two tables: Invoice and InvoiceItem (one row per item per invoice).

On the second table, neither the InvoiceNumber nor LineNumber would be sufficient (1 invoice has multiple lines, and line #1 exists on every invoice) so a COMPOUND primary key would often be used.

[Thread1]

or you could have this set of tables:

Player
Weapon
Mask
Car
PlayerWeapons
PlayerMasks

PlayerWeapons is the relationship table (one-to-many) that connects Player and Weapon table, same goes with PlayerMasks

ex.

CREATE TABLE PlayerWeapons (
PlayerWeaponsKey IDENTITY int,
PlayerKey int REFERENCES Player (PlayerKey),
WeaponKey int REFERENCES Weapon (WeaponKey)
)


HTH

[TheCPUWizard]

or you could have this set of tables:

Player
Weapon
Mask
Car
PlayerWeapons
PlayerMasks

PlayerWeapons is the relationship table (one-to-many) that connects Player and Weapon table, same goes with PlayerMasks

That would be more appropriate for decomponsing a M:M relationship (one person has many weapons, and the same weapon is shared by multiple persons. This would give a 1:M:1 relationship.

If the relationship is truely a 1:M then the addition of the table merely increases overhead, and may have a performance impact.

Remember to ALWAYS consider the 5 forms of normalization, and deviate only when there is a solid reason for doing so.

[Thread1]

yeah, but i prefer tables which are normalized on a certain degree as it balances the operation and consistency of the database.

[TheCPUWizard]

yeah, but i prefer tables which are normalized on a certain degree as it balances the operation and consistency of the database.

There are 5 recognized forms of normalization. Moving a single valued field (that is not of a "large" size, nor is extremelyt infrequently used) is against all of them.

[Thread1]

There are 5 recognized forms of normalization. Moving a single valued field (that is not of a "large" size, nor is extremelyt infrequently used) is against all of them.

can you elaborate more? because as far as i know if you don't normalized it on a certain degree/level/form you'll get duplicates on some of the records which may be the cause of logical inconsistencies.

[TheCPUWizard]

can you elaborate more? because as far as i know if you don't normalized it on a certain degree/level/form you'll get duplicates on some of the records which may be the cause of logical inconsistencies.

HEre is a decent link to get started on normalization http://www.bkent.net/Doc/simple5.htm

The basic problem, is that your approach would allow the same weapon to be SHARED by two Warriors, unless you add additional constraints to the WarriorWeapons table.

If you follow standard approaches (where the WarriorID is an FK directly in the Weapons table), you have completely eliminated this condition, and the overhead associated with inforcing a contraint. This is simply because a single field can ONLY hold a single value at one time.

Also consider the additional complexities you induce if a weapon is going to be owned by "nobody"...

Now a decent form of normalization would be to look for fields that are common to all weapons of a given type. This SHOULD be normalized to a table, and the Weapon table only containing a single FK into the WeaponType table.

[PeejAvery]

@Thread1 & TheCPUWizard

If you have more discussions, please take them to PM. This conversation has deviated from the original poster's questions. It would be best to not have him confused by all the extra side comments.

Thanks!

[Thread1]

HEre is a decent link to get started on normalization http://www.bkent.net/Doc/simple5.htm

The basic problem, is that your approach would allow the same weapon to be SHARED by two Warriors, unless you add additional constraints to the WarriorWeapons table.

If you follow standard approaches (where the WarriorID is an FK directly in the Weapons table), you have completely eliminated this condition, and the overhead associated with inforcing a contraint. This is simply because a single field can ONLY hold a single value at one time.

Also consider the additional complexities you induce if a weapon is going to be owned by "nobody"...

Now a decent form of normalization would be to look for fields that are common to all weapons of a given type. This SHOULD be normalized to a table, and the Weapon table only containing a single FK into the WeaponType table.

thanks for the information TheCPUWizard.

anyway, the design that i was proposing is based on a real world scenario wherein a player can carry more than one pistol and two players can have identical pistols. it does not rely on performance alone but also on flexibility and consistency.

i find this link nice and straight http://www.datamodel.org/NormalizationRules.html

[Thread1]

@Thread1 & TheCPUWizard

If you have more discussions, please take them to PM. This conversation has deviated from the original poster's questions. It would be best to not have him confused by all the extra side comments.

Thanks!

hi @PeejAvery sorry for the side comments .. thanks