Database structure

How can I create database for the following situation? I am using SQL server 2005.
e.g.  
PartNo: A001 can be used for three equipments
Equip1
Equip2
Equip3
I want to keep these parts in a seperate locations in three stores but keeping the PartNo unique.
e.g.
Store1
Loc1-Equip1 (10 qty.)
Loc2-Equip2 (5 qty.)
Loc3-Equip3 (25 qty.)

Store2
Loc11-Equip1 (110 qty.)
Loc22-Equip2 (25 qty.)
Loc33-Equip3 (325 qty.)

Store3
Loc111-Equip1 (100 qty.)
Loc222-Equip2 (50 qty.)
Loc333-Equip3 (250 qty.)

Please help.

Ayha
LVL 7
ayha1999Asked:
Who is Participating?
 
dqmqCommented:
Store
 StoreID (PK)
 StoreName

Equipment
  EquipmentID (PK)
  EquipmentName


StoreLocation
  StoreID(PK)
  LocationNo(PK)
  LocationName

StoreEquipmentLocation
  StoreID (PK)
  EquipmentID (PK)
  LocationNo(PK)
  Quantity

0
 
santhimurthydCommented:
Some changes to dgmq recomendation from my point of view

Store
 StoreID (PK)
 StoreName

Equipment
  EquipmentID (PK)
  EquipmentName
  Quantity

StoreLocation
  LocationNo(PK)
  LocationName

StoreEquipmentLocation
  StoreID (FK)  (Reference Store Table)
  EquipmentID (FK) (References Equipment Table)
  LocationNo(FK)(References Location)

0
 
santhimurthydCommented:
More refined one

StoreLocation
  LocationNo(PK)
  LocationName
  Address
  City/State

Store
 StoreID (PK)
 LocationNo(FK)(References Location)
 StoreName

Equipment
  EquipmentID (PK)
  StoreID (FK)  (Reference Store Table)
  EquipmentName
  Quantity
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ayha1999Author Commented:
Other than equipmentNo, I have a unique PartNo (used for different equipments) which is not mentioned in the above tables.

Please check.

Thanks.
0
 
ayha1999Author Commented:
PartNo PartName  Category
A001    some name   some category

the above part can be used for

EqupId1, EquipId2 and EquipId3

and need to store these in different store and different locations as I mentioned in the first post.  These equipments must be attached with a PartNo.



0
 
santhimurthydCommented:
StoreLocation
  LocationNo(PK)
  LocationName
  Address
  City/State

Store
 StoreID (PK)
 LocationNo(FK)(References Location)
 StoreName

PartDetails
PartNo( PK)
PartDesription

Equipment
  EquipmentID (PK)
  StoreID (FK)  (Reference Store Table)
  PartNo( FK)   (Reference PartDetails Table)
  EquipmentName
  Quantity

I have created an separate tabel for the 'PartDetails" but it's about the inforamtion it's to be saved.
If you have more additional information ralted to an specific part then create an separate table as in teh schema and add your fields. If not just addd an Extra column in the "Equipment" table to save the single value field
0
 
dqmqCommented:
For Part, in addition to my original tables:

Part
  PartNo (PK)
  PartName
  PartCategoryID

EquipmentPart
  EquipmentID(PK)
  PartNo(PK)
 

PartCategory
  PartCategoryID (PK)
  PartCategoryName
0
 
ayha1999Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.