Help needed joining 7 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help needed joining 7 tables

Author

Topic

spnz
Starting Member

12 Posts

Posted - 2006-05-13 : 08:33:52

Hi there

I am pretty new to SQL and need some help.

I am trying to make a database that is used as a tool in a glazing company.

I don't know if I am doing this the right way or not.

I have broken down the different products into 7 different tables as I believe they should not all be togeather as they all have different information.

The tables

tblBusWindScreen
tblCarWindscreen
tblTruckWindcreen
tblFlatGlass
tblTint
tblMoulds
tblSundries

I also have a table called tblOrders. (This is where all the order information will be kept.)

My question is how do I form a relationship between the 7 tables and tblOrders?

Do I add another table which I join all the PK from each of product tables then create a join between that table and tblOrders?

Any tips would be great as I am only used to creating tables that have 1 maybe 2 different tables joined to them.

Thanks for your help.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-13 : 09:40:27

Create a generic product table, in this table you put information that is common to all products.
Then link the specific product tables with a 1-1 relationship to the generic table.
The product key is generated in the generic table.

Link the orders with the generic product table.

rockmoose

spnz
Starting Member

12 Posts

Posted - 2006-05-13 : 10:06:05

hey Rockmoose.

Thank you!

Ok so let me try this out

I now have a table called tblProducts

containing

ProductID (Primary Key)
ProductName
PartNumber
Description
TradePrice
RetailPrice
SupplierID

Each of the windscreen share the same information so I make another table called tblWindscreens

WindscreenID
VType
YearFrom
YearTo
SizeW
SizeH
FitMethod

I then have another tabled called tblFlatGlass
GlassID
GlassLength
GlassWidth
Thickness
Weight

Another table tblTint
TintID
Colour

So now im a little confused on how I can join my 3 tables to tblProducts and then be able to join that to tblOrders.

Thanks for your help!

Kind regards
Shane

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-13 : 10:37:42

Have a column in Product Table to identify the product type weather it is Windscreen or FlatGlass.
Also have the WindscreenID etc as foreign key in Product Table. If the product is of Windscreen, the WindscreenID in Product table will have a value else it is NULL.

ProductID (Primary Key)
ProductName
PartNumber
Description
TradePrice
RetailPrice
SupplierID
ProductType -- identify it is a Windscreen, Glass
WindscreenID <FK>
GlassID <FK> 
TintID <FK>

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-13 : 10:46:09

For example the windscreen table:
ProductID primary key references tblProducts(ProductID)
VType
YearFrom
YearTo
SizeW
SizeH
FitMethod

The key is the same as the key in tblProducts.
When creating a windscreen, you insert first into tblProducts and then into tblWindscreens.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-13 : 10:51:43

quote:
Originally posted by khtan

Have a column in Product Table to identify the product type weather it is Windscreen or FlatGlass.
Also have the WindscreenID etc as foreign key in Product Table. If the product is of Windscreen, the WindscreenID in Product table will have a value else it is NULL.
ProductID (Primary Key)
ProductName
PartNumber
Description
TradePrice
RetailPrice
SupplierID
ProductType -- identify it is a Windscreen, Glass
WindscreenID <FK>
GlassID <FK>
TintID <FK>

I don't agree at all with the statement above.
The FK is in the "subtables".
Also the productType column is one that I ommit.
If the product is of type "windscreen", this is explicit in the model, since it has a corresponding row in the windscreen table.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources