urgent ques. in DB design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

urgent ques. in DB design

Author

Topic

tareq
Starting Member

3 Posts

Posted - 2008-10-27 : 13:43:38

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:49:36

why have you included playerid in each of tables like weapon,mask ...?you already have all of their PK columns in player table which represents relation.

tareq
Starting Member

3 Posts

Posted - 2008-10-27 : 18:47:44

I think they should exist in these tables as the FKs ! does things done like this ? or what ?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 19:41:15

it really depends..

You don't need FK in these tables when any Weapon or mask can be used by 1 or more players.

I'd think You'd create one type of mask/weapon that is suitable for more than 1 players, in this case you don't need PlayerID in these tables.

You could also drop All the IDs under Player and leave player table as:

PLAYER
PlayerID (PK), NAME, etc...

Weapon
WeaponID (PK), PlayerID (FK), weapontype etc...

Mask
MaskID (PK), PlayerID (FK), masktype, etc..

Car
CARID(PK), PlayerID (PK,FK), cartype, etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources