| Groups | Blog | Home |
sql server programming : design question
Here is the scenario where a soldier can have only 1 weapon. As long as it's an atom bomb, I gues that's ok. I handle weapon attributes dirrerently so that all weapon attributes can be stored in one table with an unlimited number of attributes per weapon: -- -- scenario where a soldier can have only one weapon -- CREATE TABLE WeaponTypes ( WeaponID int identity(1,1) PRIMARY KEY ,Type nvarchar(50) ) GO CREATE TABLE Soldiers ( SoldierID int identity(1,1) PRIMARY KEY ,SoldierName nvarchar(50) ,WeaponID int REFERENCES WeaponTypes(WeaponID) ) GO CREATE TABLE WeaponAttributes ( WeaponID int REFERENCES WeaponTypes(WeaponID) ,Attribute nvarchar(50) ,AttributeValue sql_variant ) GO INSERT WeaponTypes(Type) VALUES('Gun') INSERT WeaponTypes(Type) VALUES('Knife') go INSERT WeaponAttributes(WeaponID, Attribute, AttributeValue) select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Caliber' ,'9mm' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Color' ,'Black' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Magazine Capacity' ,13 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Knife') ,'Length in inches' ,9 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Number of edges' ,2 go INSERT Soldiers(SoldierName, WeaponID) select SoldierName='G.I. Joe', WeaponID=(select WeaponID from WeaponTypes where Type='Knife') union all select SoldierName='Audy Murphy', WeaponID=(select WeaponID from WeaponTypes where Type='Gun') union all select SoldierName='Sgt. Rock', WeaponID=(select WeaponID from WeaponTypes where Type='Gun') union all select SoldierName='Pvt. Benjamin', WeaponID=(select WeaponID from WeaponTypes where Type='Knife') go -- -- scenario where a soldier can have multiple weapons -- but not more than 1 of the same type. This is arbitrary -- and you can allow more than 1 of the same type by -- removing the PRIMIARY KEY CONSTRAINT on SoldierWeapons -- CREATE TABLE WeaponTypes ( WeaponID int identity(1,1) PRIMARY KEY ,Type nvarchar(50) ) GO CREATE TABLE WeaponAttributes ( WeaponID int REFERENCES WeaponTypes(WeaponID) ,Attribute nvarchar(50) ,AttributeValue sql_variant CONSTRAINT WA_PK PRIMARY KEY (WeaponID, Attribute) ) GO CREATE TABLE Soldiers ( SoldierID int identity(1,1) PRIMARY KEY ,SoldierName nvarchar(50) ) GO CREATE TABLE SoldierWeapons ( SoldierID int REFERENCES Soldiers(SoldierID) ,WeaponID int REFERENCES WeaponTypes(WeaponID) CONSTRAINT SQ_PK PRIMARY KEY (SoldierID, WeaponID) ) GO INSERT WeaponTypes(Type) VALUES('Gun') INSERT WeaponTypes(Type) VALUES('Knife') INSERT WeaponTypes(Type) VALUES('Ray Gun') go INSERT WeaponAttributes(WeaponID, Attribute, AttributeValue) select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Caliber' ,'9mm' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Color' ,'Black' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Magazine Capacity' ,13 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Knife') ,'Length in inches' ,9 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Number of edges' ,2 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') ,'Setting 1' ,'Stun' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') ,'Setting 2' ,'Fade To Black' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') ,'Setting 3' ,'Explode' go INSERT Soldiers(SoldierName) select SoldierName='G.I. Joe' union all select SoldierName='Audy Murphy' union all select SoldierName='Sgt. Rock' union all select SoldierName='Pvt. Benjamin' go INSERT SoldierWeapons(SoldierID, WeaponID) select SoldierID=(select SoldierID from Soldiers where SoldierName='G.I. Joe') ,WeaponID=(select WeaponID from WeaponTypes where Type='Gun') union all select SoldierID=(select SoldierID from Soldiers where SoldierName='G.I. Joe') ,WeaponID=(select WeaponID from WeaponTypes where Type='KNIFE') union all select SoldierID=(select SoldierID from Soldiers where SoldierName='Sgt. Rock') ,WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') -- -- scenario where a soldier can have only one weapon -- CREATE TABLE WeaponTypes ( WeaponID int identity(1,1) PRIMARY KEY ,Type nvarchar(50) ) GO CREATE TABLE Soldiers ( SoldierID int identity(1,1) PRIMARY KEY ,SoldierName nvarchar(50) ,WeaponID int REFERENCES WeaponTypes(WeaponID) ) GO CREATE TABLE WeaponAttributes ( WeaponID int REFERENCES WeaponTypes(WeaponID) ,Attribute nvarchar(50) ,AttributeValue sql_variant ) GO INSERT WeaponTypes(Type) VALUES('Gun') INSERT WeaponTypes(Type) VALUES('Knife') go INSERT WeaponAttributes(WeaponID, Attribute, AttributeValue) select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Caliber' ,'9mm' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Color' ,'Black' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Magazine Capacity' ,13 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Knife') ,'Length in inches' ,9 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Number of edges' ,2 go INSERT Soldiers(SoldierName, WeaponID) select SoldierName='G.I. Joe', WeaponID=(select WeaponID from WeaponTypes where Type='Knife') union all select SoldierName='Audy Murphy', WeaponID=(select WeaponID from WeaponTypes where Type='Gun') union all select SoldierName='Sgt. Rock', WeaponID=(select WeaponID from WeaponTypes where Type='Gun') union all select SoldierName='Pvt. Benjamin', WeaponID=(select WeaponID from WeaponTypes where Type='Knife') go [quoted text, click to view] "psawant@gmail.com" wrote:
> I have design question. thanks in advance.. > this is what i need to store. > > > i need to store soldierinfo.(name,id,weapontype). each soldier is > allowed one weapon. > > > weapon could be a knife or gun or and it could be anything else. > > > guns(calibre,name) and knife(length,'name') have different > properties. > > > what would be the best way to design. > > also how woudl this change if i want to allow a soldier to have more > than one weapon.. >
OK, here is how to handle multiple weapons, but only 1 of a particular type.
Now for something really cool, oops my boss is coming..... -- -- scenario where a soldier can have multiple weapons -- but not more than 1 of the same type. This is arbitrary -- and you can allow more than 1 of the same type by -- removing the PRIMIARY KEY CONSTRAINT on SoldierWeapons -- CREATE TABLE WeaponTypes ( WeaponID int identity(1,1) PRIMARY KEY ,Type nvarchar(50) ) GO CREATE TABLE WeaponAttributes ( WeaponID int REFERENCES WeaponTypes(WeaponID) ,Attribute nvarchar(50) ,AttributeValue sql_variant CONSTRAINT WA_PK PRIMARY KEY (WeaponID, Attribute) ) GO CREATE TABLE Soldiers ( SoldierID int identity(1,1) PRIMARY KEY ,SoldierName nvarchar(50) ) GO CREATE TABLE SoldierWeapons ( SoldierID int REFERENCES Soldiers(SoldierID) ,WeaponID int REFERENCES WeaponTypes(WeaponID) CONSTRAINT SQ_PK PRIMARY KEY (SoldierID, WeaponID) ) GO INSERT WeaponTypes(Type) VALUES('Gun') INSERT WeaponTypes(Type) VALUES('Knife') INSERT WeaponTypes(Type) VALUES('Ray Gun') go INSERT WeaponAttributes(WeaponID, Attribute, AttributeValue) select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Caliber' ,'9mm' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Color' ,'Black' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Magazine Capacity' ,13 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Knife') ,'Length in inches' ,9 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') ,'Number of edges' ,2 union all select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') ,'Setting 1' ,'Stun' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') ,'Setting 2' ,'Fade To Black' union all select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') ,'Setting 3' ,'Turn target into a poodle.' go INSERT Soldiers(SoldierName) select SoldierName='G.I. Joe' union all select SoldierName='Audy Murphy' union all select SoldierName='Sgt. Rock' union all select SoldierName='Pvt. Benjamin' go INSERT SoldierWeapons(SoldierID, WeaponID) select SoldierID=(select SoldierID from Soldiers where SoldierName='G.I. Joe') ,WeaponID=(select WeaponID from WeaponTypes where Type='Gun') union all select SoldierID=(select SoldierID from Soldiers where SoldierName='G.I. Joe') ,WeaponID=(select WeaponID from WeaponTypes where Type='KNIFE') union all select SoldierID=(select SoldierID from Soldiers where SoldierName='Sgt. Rock') ,WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') -Mike [quoted text, click to view] "psawant@gmail.com" wrote:
> I have design question. thanks in advance.. > this is what i need to store. > > > i need to store soldierinfo.(name,id,weapontype). each soldier is > allowed one weapon. > > > weapon could be a knife or gun or and it could be anything else. > > > guns(calibre,name) and knife(length,'name') have different > properties. > > > what would be the best way to design. > > also how woudl this change if i want to allow a soldier to have more > than one weapon.. >
This looks like a case of sub-typing. Create a generic weapons table and
then subtype off of it. Create a FK from SoldierInfo to it. Include the type of weapon in the FK. Here's a rough sketch: Weapons WeaponID Type Name PK = WeaponID UK = (WeaponID, Type) Knives WeaponID Type Length PK = (WeaponID, Type) FK = (WeaponID, Type) references Weapons Guns ID Type Calibre PK = (WeaponID, Type) FK = (WeaponID, Type) references Weapons SoldierInfo SoldierID Name WeaponID Type PK = SoldierID FK = (WeaponID, Type) references Weapons The above schema supports only one weapon per soldier. Now, if you want > 1 weapon per soldier, you can go two ways. One is to allow only one of each type of weapon per soldier. The other is to allow multiple weapon types per soldier, e.g. 3 knives and two guns. In both cases, you remove the weapon info from the SoldierInfo table: SoldierInfo SoldierID Name PK = SoldierID Now, you need an associative object between SoldierInfo and Weapons: SoldierWeapons SoldierID WeaponID Type FK = SoldierID FK = (WeaponID, Type) references Weapons As for the PK, it depends on how many of each weapon type you want to allow: One of each weapon type: PK = (SoldierID, Type) Many weapon types: PK = (SoldierID, WeaponID) HTH -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau [quoted text, click to view] <psawant@gmail.com> wrote in message I have design question. thanks in advance..news:1186091171.789504.228500@q3g2000prf.googlegroups.com... this is what i need to store. i need to store soldierinfo.(name,id,weapontype). each soldier is allowed one weapon. weapon could be a knife or gun or and it could be anything else. guns(calibre,name) and knife(length,'name') have different properties. what would be the best way to design. also how woudl this change if i want to allow a soldier to have more than one weapon..
I have design question. thanks in advance..
this is what i need to store. i need to store soldierinfo.(name,id,weapontype). each soldier is allowed one weapon. weapon could be a knife or gun or and it could be anything else. guns(calibre,name) and knife(length,'name') have different properties. what would be the best way to design. also how woudl this change if i want to allow a soldier to have more than one weapon..
Thanks all.. for all the input.... . its been a great help...
And no i am not developing some game... i just drew a soldier-weapon analogy to represent my problem.. Thanks once again.. On Aug 2, 6:42 pm, Mike Whiting [quoted text, click to view] <MikeWhit...@discussions.microsoft.com> wrote: > OK, here is how to handle multiple weapons, but only 1 of a particular type. > Now for something really cool, oops my boss is coming..... > > -- > -- scenario where a soldier can have multiple weapons > -- but not more than 1 of the same type. This is arbitrary > -- and you can allow more than 1 of the same type by > -- removing the PRIMIARY KEY CONSTRAINT on SoldierWeapons > -- > CREATE TABLE WeaponTypes > ( > WeaponID int identity(1,1) PRIMARY KEY > ,Type nvarchar(50) > ) > GO > CREATE TABLE WeaponAttributes > ( > WeaponID int REFERENCES WeaponTypes(WeaponID) > ,Attribute nvarchar(50) > ,AttributeValue sql_variant > CONSTRAINT WA_PK PRIMARY KEY (WeaponID, Attribute) > ) > GO > CREATE TABLE Soldiers > ( > SoldierID int identity(1,1) PRIMARY KEY > ,SoldierName nvarchar(50) > ) > GO > CREATE TABLE SoldierWeapons > ( > SoldierID int REFERENCES Soldiers(SoldierID) > ,WeaponID int REFERENCES WeaponTypes(WeaponID) > CONSTRAINT SQ_PK PRIMARY KEY (SoldierID, WeaponID) > ) > GO > INSERT WeaponTypes(Type) VALUES('Gun') > INSERT WeaponTypes(Type) VALUES('Knife') > INSERT WeaponTypes(Type) VALUES('Ray Gun') > go > INSERT WeaponAttributes(WeaponID, Attribute, AttributeValue) > select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') > ,'Caliber' > ,'9mm' > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') > ,'Color' > ,'Black' > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') > ,'Magazine Capacity' > ,13 > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Knife') > ,'Length in inches' > ,9 > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Gun') > ,'Number of edges' > ,2 > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') > ,'Setting 1' > ,'Stun' > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') > ,'Setting 2' > ,'Fade To Black' > union all > select WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') > ,'Setting 3' > ,'Turn target into a poodle.' > go > INSERT Soldiers(SoldierName) > select SoldierName='G.I. Joe' > union all > select SoldierName='Audy Murphy' > union all > select SoldierName='Sgt. Rock' > union all > select SoldierName='Pvt. Benjamin' > go > INSERT SoldierWeapons(SoldierID, WeaponID) > select > SoldierID=(select SoldierID from Soldiers where SoldierName='G.I. Joe') > ,WeaponID=(select WeaponID from WeaponTypes where Type='Gun') > union all > select > SoldierID=(select SoldierID from Soldiers where SoldierName='G.I. Joe') > ,WeaponID=(select WeaponID from WeaponTypes where Type='KNIFE') > union all > select > SoldierID=(select SoldierID from Soldiers where SoldierName='Sgt. Rock') > ,WeaponID=(select WeaponID from WeaponTypes where Type='Ray Gun') > > -Mike > > > > "psaw...@gmail.com" wrote: > > I have design question. thanks in advance.. > > this is what i need to store. > > > i need to store soldierinfo.(name,id,weapontype). each soldier is > > allowed one weapon. > > > weapon could be a knife or gun or and it could be anything else. > > > guns(calibre,name) and knife(length,'name') have different > > properties. > > > what would be the best way to design. > > > also how woudl this change if i want to allow a soldier to have more > > than one weapon..- Hide quoted text - > > - Show quoted text -
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |