DB Design Help

Last Post 08 Aug 2006 09:40 PM by SQLUSA. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
billthecat0702
New Member
New Member

--
04 Aug 2006 06:57 AM
I need help with my initial DB design...

I have a spreadsheet I am converting to a database and I need help designing and relating some tables.

I have created a table of Contacts with Name and several Phone Numbers.

Next, I need a Table of Equipment that includes things like EquipmentID, Asset Tag #, and then I need a list of people to be contacted in order for that peice of equipment.

How do I create my tables and setup my relationships so when I search for a peice of equipment, it returns the equipment data and the contact list in the order they should be called?

Thanks,

Don Brown
mwesch
New Member
New Member

--
04 Aug 2006 03:28 PM
There are a lot of variations you could do depending on all your business rules. However, fundamentally, you have defined 3 tables.

Equipment
-----------------
EquipmentID (pkey)
AssetTag
Description

Contact
-----------------
ContactID (pkey)
Name
Phone

EquipmentContact
-----------------
EquipmentID (pkey)(fkey to Equipment)
ContactID (pkey)(fkey to Contact)
ContactPriority --this could be an integer from 0-100 indicating who to contact first, second, etc.
SQLUSA
New Member
New Member

--
06 Aug 2006 04:20 PM
Nice design Michael!

The EquipmentContact table represents many-to-many relatinship.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World
billthecat0702
New Member
New Member

--
07 Aug 2006 09:19 AM
OK. The design is setup and the Relationships are done... Now I am having trouble writing the query to pull the info I need.

I need a query that will pull the data from all 3 tables starting with Equipment like this:
SELECT * FROM Equipment WHERE EquipmentID = '1'

then pull all records from the EquipmentContact Table with the same Equipment ID like this:
SELECT * FROM EquipmentContact WHERE EquipmentContact.EquipmentID = Equipment.EquipmentID

Then pull all contacts that have the same ContactID like this:
SELECT * FROM Contacts WHERE Contacts.ContactID = EquipmentContact.ContactID ORDER BY ContactPriority

so I get output that looks like
EquipmentID Asset Tag Description
------------------------------------------
M343171 1234567 -80 degree Freezer

Priority ContactID Name Phone
-------------------------------------------------------
1 1 Jim Swanson 812-555-1212
2 15 Julie Yen 317-555-2424
3 6 Sam Jenkins 317-555-4848


Thank You - You've been a big help already. I had already designed what you recommended, so I know I am on the right track.
SQLUSA
New Member
New Member

--
07 Aug 2006 03:00 PM
SELECT (whatever you want)
FROM EQUIPMENT e
JOIN EQUIPMENTCONTACT ec
on e.EquipmentID=ec.EquipmentID
JOIN CONTACT c
on c.ContactID = ec.ContactID
WHERE (whatever you need)
ORDER BY (whichever way you desire)


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World
GoldenGal
New Member
New Member

--
08 Aug 2006 08:25 AM
Hello Don,

Take a look at my article "Multivalued Attributes" from March 2002, InstantDoc # 23734 -- the model in this article might be just what you're looking for -- actually, the model in Figure 3 is what you're looking for. If you need to build out the Vendor portion of this model, then hop on over to InstantDoc #5340, June 1999 -- that's a model for Contact Management. Integrating the two is pretty straightforward -- I'll leave that to you!

Good luck, and have fun,
Michelle
SQLUSA
New Member
New Member

--
08 Aug 2006 09:40 PM
Pardon my ignorance Michele.

How do we get to the instant articles?

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy