Brain Dead in Designing Tables - This Is Tough 2

[forrest33]

I have a big list of Companies, CompDirectory. Each of them has at least one NAICS code (six digit industry code)but no more than 6.

CompID (primary key)
CompName
Street
City etc.
NAICS1
Naics2
Naics3
Naics4
Naics5
Naics6

I've got a second table, ProductCategory, where each NAICS code has its corresponding description/title:

ProductId (primary Key)
ProductTitle (eg: Battery Mfg)
NAICS (eg: 332620)

I need to be able to query by ProductTitle and view each company that makes that product.
One company can have up to 6 Naics and one Naics can have multiple companies, hence, it's a many to many, but cannot figure out how to design the damn tables and keep thinking I need a third table.
Maybe you guys can help, I am stuck on it for a week now and the thick fog has not lifted yet...

 

[lespaul]

You're right, you need a third table:

Company
CompID
Name
Address
etc.

ProductCategory
ProductID
Title
NAICS

CompanyNAICS
CompanyID (FK) }
NAICS (FK) } Composite Primary Key


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[forrest33]

Leslie,

I'm still confused.
In the CompanyNAICS table, you designed NAICS as the FK, but this is not a PK in the ProductCategory table... Do I even need a ProductID PK in the ProductCategory table, or can NAICS be the PK?


Thanks.

 

[lespaul]

depends on what the requirements are. A FK in one table is not required to be the primary key in another. Can each Product have only one NAICS? Can an NAICS have more than one Product Title? Have you read the Fundamentals document link?

With the design I laid out, you won't have your NAICS1 - NAICS6 in your Company Table (that's committing spreadsheet in your database) it will only be in the CompanyNAICS table.

Does that help clear things up any?

leslie

 

[forrest33]

Leslie,

1 Company = up to 6 NAICS codes
1 NAICS code = multiple Companies
1 ProductTitle = 1 NAICS code

I read Paul Litwin's Fundamentals more than once. Can't see the solution.
If my CompanyTbl does not display the NAICS codes, how can I retrieve the corresponding ones for each company?

 

[johnherman]

The table Les is suggesting is quite common in relational database design. However, it has many names. It can and has been called a relation table, juction table, bridge table, associative table, etc.

The point is that to resolve the many to many (or in this case 1-6 to many relationship, we need to have a separate table associating the primary keys of the two many to many tables in the "bridge" table.

So, to see which companies have a particular industry code, go to the bridge table and select based on the code. To see which industry codes a company is associated with, go to the bridge (or associative seeing how I have used that word) table and look up all the codes for that company.

The bridge table is a cleaner and more general solution that having 6 "buckets" in the company record to hold the industry codes, although it is not as efficient in terms of performance. What if, for instance, it is suddenly permissible to have 8 industry codes with each company. The bridge table solution can already handle it while some modifications will be needed for the "bucket" design.

Don't get me wrong, buckets are sometimes useful and are better performing than bridge tables. For instance, a bucket for each quarter or month of the year, or each day of the week is not a bad idea, since it is unlikely that we will be adding adding days to the week or months to the year.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[lespaul]

If my CompanyTbl does not display the NAICS codes, how can I retrieve the corresponding ones for each company

you do that in the query.

Let's say you have:

CompanyID           CompanyName
1                   Joe Blow's Barber Shop
2                   Jane Doe's Hair Salon

ProductCategory
ProductID              Title            NAICS
1                   Hair Cut            12345
2                   Blow Dry            23456
3                   Color               45678
4                   Highlights          56789

CompanyNAICS
CompanyID              NAICS
1                      12345
2                      12345
2                      23456
2                      45678
2                      56789

Then this query:

SELECT CompanyName, NAICS, Title
FROM CompanyTable
INNER JOIN ProductCategory on CompanyNAICS.NAICS = ProductCategory.NAICS
INNER JOIN CompanyNAICS on CompanyTable.CompanyID = CompanyNAICS.CompanyID

will return

Joe Blow's Barber Shop    12345   Hair Cut
Jane Doe's Hair Salon     12345   Hair Cut
Jane Doe's Hair Salon     23456   Blow Dry 
Jane Doe's Hair Salon     45678   Color
Jane Doe's Hair Salon     56789   Highlights

It's a matter of joining the tables correctly to get the data you need. Have you read Understanding SQL Joins?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

1 ProductTitle = 1 NAICS code
So, the NAICS code is a good candidate for PK in the product table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[forrest33]

Nope, not working. Empty fields in the third table.
Thanks to everyone trying this with me.
Time to bang my head against the wall....

 

[lespaul]

what do you mean, 'empty fields in the third table'? You will have to populate the fields in the table (either manually or as part of your process).


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[BobRodes]

A little point, forrest: there are three things that you do with data. You enter it, you store it, you get it back out. Each one of these things does NOT repeat does NOT determine how the other does its business. You store data according to the proper way to store data. You do NOT store data according to what you want to show, or how you want to enter it. This is very important, and the chief stumbling block among students in my experience.

The fact that you have a limit of 6 does not imply any structural considerations. It implies a constraint as to what data may go into that structure.

Read lespaul's article, and note that your structure breaks the first normal form. johnherman gives a good explanation of why that can be a problem. (He also gives good examples of when it might not be a problem, a process called "denormalization.") Additionally to his explanation, there's also a good deal of wasted space because a lot of those field values are going to be empty.

The information on structure here is entirely accurate. Stop banging your head against a wall, follow through on the instructions given, and you will have a good solution.

HTH

Bob

p. s. I wonder what you mean by "empty fields in the third table" too.

 

[forrest33]

CompanyTbl
CompanyID(PK) - one to many w. CompanyID in CompanyNAICStbl
CompName
Street
City
NAICS1
NAICS1
NAICS3
NAICS4
NAICS5
NAICS6

NAICSTbl
Title
NAICS(PK) - one to many w. NAICS in CompanyNAICStbl

CompanyNAICSTbl
CompanyID (CompositePK)
NAICS (CompositePK)
This table is not populated. The other two are.

 

[lespaul]

ok, you need to remove
NAICS1
NAICS2
etc

from CompanyTbl

You could run an INSERT query into CompanyNAICSTbl before deleting those fields and then you wouldn't have to manually enter the data again.

Create a UNION query that normalizes the existing data:

SELECT CompanyID, NAICS1 FROM CompanyTbl WHERE NAICS1 <> ''
UNION
SELECT CompanyID, NAICS2 FROM CompanyTbl WHERE NAISC2 <> ''
...
UNION
SELECT CompanyID, NAICS6 FROM CompanyTbl WHERE NAICS6 <> ''

Save that query (qryNormalized) and use it as the source of the insert query:

INSERT INTO CompanyNAICS (SELECT * FROM qryNormalized)

Now delete the fields from CompanyTbl. Now you have a normalized structure.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[forrest33]

Thanks, Leslie,

Followed your advice ad literam.
Ran into problems with the Union Query - it will only pick NAICS1, NAICS3 and NAICS5 for some reason. I kept looking long and hard at the code, seems fine to me.
The Append Query does not like the NAICS1 field. I got an error message about it, but it could also be because of the faulty Union Query.

 

[lespaul]

could you post the SQL of both queries?

 

[BobRodes]

<ok, you need to remove
<NAICS1
<NAICS2
<etc

Because it breaks the first normal form, as a perusal of lespaul's article will reveal to you. You may not feel you have the time to read it since you're under the gun, which is understandable. However, lespaul is right, it or something like it is to be considered essential reading.

<seems fine to me.

Why? It doesn't work.

Bob

 

[forrest33]

SELECT CompanyID, NAICS1 from 06CompanyDirectorywNaics where NAICS1<>
"
UNION
Select CompanyID, NAICS2 from 06CompanyDirectorywNaics where NAICS2<>
"
UNION
Select CompanyID, NAICS3 from 06CompanyDirectorywNaics where NAICS3<>
"
UNION
Select CompanyID, NAICS4 from 06CompanyDirectorywNaics where NAICS4<>
"

UNION Select CompanyID, NAICS5 from 06CompanyDirectorywNaics where NAICS5<>
"
UNION
Select CompanyID, NAICS6 from 06CompanyDirectorywNaics where NAICS6<>
";

For some reason, only NAICS 1,3 and 5 are showing in the CompanyNaics table. NAICS 2, 4 and 6 are not. Makes me think I should rename the fields.

INSERT INTO CompanyNAICS
SELECT qryNormalized.*
FROM qryNormalized;

This one gives me an error msg about the NAICS1

 

[pbrodsky]

For some reason, only NAICS 1,3 and 5 are showing in the CompanyNaics table. NAICS 2, 4 and 6 are not. Makes me think I should rename the fields...

In the posteds SQL, I believe the '' is supposed to be 2 single quotes rather than 1 double quote

 

[forrest33]

Thanks to pbodsky, my Union Query (qryNormalized) is working fine now.
I'm still working on the Append Query, maybe you guys can see errors I'm blind to.

 

[lespaul]

ok, try this:

modify qryNormalized by changing just the FIRST query (getting NAICS1):

SELECT CompanyID [b]as CompanyID[/b], NAICS1 [b]As NAICS[/b] ....

then in your INSERT query try:

INSERT INTO CompanyNAICS (SELECT CompanyID, NAICS FROM qryNormalized)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design