Basic Database Relationship

Question

Post reply

Basic Database Relationship

Jonah-356166

SSC Enthusiast

Points: 112
More actions
March 18, 2010 at 5:42 am

#221927

Hi Guy's I'm having trouble trying to get my head around this one but I know it is a very simple solution. Wondering if someone could help me. I have a table with product codes and items related to the codes.
TABLE 1
Product Codes | Item Number
RP WOEWPOI
Y OWRT0W
ZZ LDSFLSGKK
I have created a second table which contains a product group. This table holds information on where the Product Codes belong. I'm not sure if I have designed this table correctly.
TABLE 2
Product Group | Product Codes | Product Codes1 | Product Codes2 |Product Codes3 .........etc etc
Tin RP TL ER AS
Metal Y ZZ
Water EF TY AS
Coffee OP RC
My aim is to create a report in where I know a particular item is related to a certain Product Group. I'm not sure if I'm going about it the right way though. I need to know how to link it if it's one to many or many to many or...I really don't know.
Your help would be much appreciated.
Jonah

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

from a quick look you need to normalise the second table.

en.wikipedia.org/wiki/Database_normalization

For example rather than having.

Product Group | Product Codes | Product Codes1 | Product Codes2 |Product Codes3 .........etc etc

you should have

Product Group | ProductCodeType | ProductCode

the data should then look like;

Tin 1 RP

TIN 2 TL

TIN 3 ER

etc..

then you can do a join based on the product code, and the table is a lot more flexible for future changes such as adding new Prodcut Code Type

Jonah-356166 SSC Enthusiast Points: 112 More actions · Answer 2

Would the ProductType Number start over agin when there is a new product Group?

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

You're correct, you did not design the table correctly. What would happen if you need to add one more productcode? The entire database and the front-end would break.

Anytime that you have more then one item that relates to only one item. Such as cars and colors the car comes in. You must use an associative table.

Example:

Car

CarRowID

MakeofCar

CarName

CarColor

ColorRowID

ColorName

CarCarColor

CarRowID

ColorRowID

That way you can have many cars with many colors. And a car can come in one or more colors, and one or more colors can be on one or more cars

You should Never, Ever create a table with a long list of items like that.

Andrew SQLDBA

Jonah-356166 SSC Enthusiast Points: 112 More actions · Answer 4

Aah ok. Table one cannot be changed as it's from a datasource. The primary key from this would be the Product Code.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

That is fine, what ever you use. You do not have change the table. I was only giving an example. You just have to make certain that the Key will not allow a duplicate. That is difficult using text.

Andrew SQLDBA

Jonah-356166 SSC Enthusiast Points: 112 More actions · Answer 6

Thanks Steve, this has helped me a lot.

Kind Regards,

Jonathan

vidya_pande SSCertifiable Points: 5275 More actions · Answer 7

Best normalized form could be

TABLE 1 {This is product code master}

Product Codes | Item Number

TABLE 2 {This is product group master}

Product Group ID | Product Group

TABLE 3 {This is mapping of Product Group ID and Product Codes}

Product Group ID | Product Codes

This will seperate out master informatoin and detailed mapping information. Data can be handled in better manner avioding delete anomalies