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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
Store
 StoreID (PK)
 StoreName

Equipment
  EquipmentID (PK)
  EquipmentName


StoreLocation
  StoreID(PK)
  LocationNo(PK)
  LocationName

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.