Table Relationships

[dmctiernan]

Hi All,
I'm pretty much learning as I go along so bear with me. I am trying to develop a database in SQL Server 2005 for tracking manufactured items on projects. We manufacture conveyor systems and I am trying to track certain manufactured item information for sales and support purposes, such as the motor/gearbox details, shaft details and belt details. However, I have now come to a point where I have been stumped on how to best implement something.

We can use different physical belt types on a conveyor. However all the information that defines each belt type is different. I'll try to explain better:

Belt Types:
1) Wire Mesh
2) Endless
3) Modular Plastic

Ideally I need to have a seperate table for each belt type because the defining information is not common to all belt types but in relation to the actual conveyor, it is simply just a belt. If the belt type is WireMesh then get the belt details from the WireMeshBelts table, or if the belt type is ModularPlastic get the belt details from the ModularPlasticBelts table and so on. How can I setup a conditional relationship like this or is this something that I really need to deal with in my interface instead (VB2005 using ADO.NET 2.0 for data access).

I hope this makes some sense to someone, if not I can try to better explain.

Cheers,
Declan.

 

[Qik3Coder]

You don't need to go to that length.

You could do your whole setup with half a dozen tables.
Start with a Conveyors Table

Conveyors
---------
ConveyorID (PK)
ConveyorName
...


Then you need a PartsList table, for all the parts that go into the conveyor. You may want to specify the order of install, if you have the ability or the time.

ConveyorParts
-------------
PartsListID(PK)
ConveyorID (FK)
PartID(FK)
Qty int

Next you need a parts table, for your parts list
You may even want to store where the parts are kept in the warehouse, so that new people can get the parts without digging around.

Parts
-----
PartID (PK)
PartName
PartDescription
PartManufacturer
PartSupplier
PartNumber
PartCost
(PartLocation)

Optionally you could have a supplier and Manufacturer table

Supplier
-------
SupplierID
SupplierPhone
SupplierWebsite
SupplierCode

(Manufacturer is similar)



When someone is piecing together a product, they don't care what the components are, only which ones they need. Your sales team may need to be able to sell a more rigid design, so they will want access to the descriptions, but the guys on the floor are just assemblers, and probably don't care.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]