Table Troubles

[justin.peskar]

Ok here is the situation. I a looking at setting up a database for my
company that tracks manufactures/potential manufactures and their
capabilities.

What I have so far is: ( * is the primary Key)

Table: Manufactures
Company ID*
Company Status ID
Company Name
Address.. Ect

Table: Contacts
Company ID* (We have more than one contact per company)
Contact ID
Contact Name
Title
Ect.


Table: Company Status
Company Status ID*
Status
Status Description


Now here is where I have problems. Each company may do many things.
Example: Blank parts, drill holes, paint, package. Each company will
then do this for many materials. Wood, metal, plastic... Then we have
all ISO standards and quality standards..

So I have the processes organized into a few table and this is where
things get fuzzy.

Table: Mfg Process
Process ID*
Process Name
Process Description

Table: Process Materials
Material ID*
Material Name
Material Description

How do I link these two tables to the Manufacture? I have tried a
linker table with

Table: Company Processes
Company ID
Process ID

But I'm not sure if that is the best way. Or should I just combine the
Mfg Process and Company Process tables into one table? I will want to
add new processes later on...

Any thoughts? Questions? Thanks for the advice!

 

[Allen Browne]

Big question, Justin. I won't pretend to have answered it: just suggesting
some alternatives for you to consider so you can find the best way.

First suggestion: Naming
Consider omitting the spaces in field/table names. It will save you having
to add square brackets and make your typing faster. (You may also want to
consider omitting the S from the end of table names, and possibly adding tbl
as a prefix so you can distinguish between tables and queries.)

Re Manufacturers and Contacts, the structure does not connect the contact to
the manufacturer. Could there ever be someone who is a contact for multiple
manufacturers? If not, you can just add a CompanyID field to your Contacts
table. Otherwise you need another table to track which contacts and
manufacturers go together.

CompanyStatus is good: you'll end up with lots of these little lookup
tables. I think your ProcessMaterial table is a lookup table as well (just
storing material types such as wood, plastic, metal.)

Your MfgProcess table is also a lookup with simple entries such as "drill",
"paint", "package"?

Then I take it than one company can perform multiple processes, and any one
process can also be performed by many companies? That's a many-to-many
relation, so you need a junction table to resolve it into a pair of
one-to-many relations. That means your CompanyProcess table is the right
approach.

I don't know, but you may need to record that company ABC has the ability to
paint wood, but cannot paint metal? If so, the CompanyProcess table would
have fields:
CompanyID
ProcessID
MaterialTypeID

HTH

 

[justin.peskar]

Thanks for the advice! I now understand Many to Many better. (I
think.... It is like quantum physics.. no one really undrstands! HA)

So here I go.

For
Table:Contacts there is a CompanyID relationship, I just forgot to put
it in. I need more sleep!

Also I really liked the idea of look-up tables for the process and
materials tables. It will allow for the expansion of more materials/
process as this grows.

So what I Have now is:
Table: Company
CompanyID*
Address
Ect

Table CompanyProcess
CompanyID
ProcessID
MaterialID
Notes

Tablercess
ProcessID*
Process
Notes

Table:Material
MaterialID
Material
Notes

They are linked

Table:Company Table:CompanyProcess
Tablerocess Table:Material
CompanyID(1)- ------------ (M) CompanyID
Company ProcessID (1)--------------------------(M)
ProcessID*
MaterialID (1)
----------------------------------------------------------------------
(M) MaterialID*

But if I set the Primary key in Table:CompanyProcess to company ID I
can only use each companyID once! Should I leave off the key for that
table or should I have multiple keys?

Thanks for the help

 

[tina]

But if I set the Primary key in Table:CompanyProcess to company ID I

can only use each companyID once! Should I leave off the key for that
table or should I have multiple keys?

each table can only have one *primary* key (though a primary key can be made
up of more than one field, if necessary). but i think you're confusing
primary keys and foreign keys here. the CompanyID field is a primary key in
the Companies table, but would be a *foreign* key in the CompanyProcess
table. suggest you read up on tables/relationships (more, if you've already
done some reading); it's important that you understand the basics of
relational design principles so you can set your tables/relationships up
correctly before you go further. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Amy Blankenship]

each table can only have one *primary* key (though a primary key can be
made
up of more than one field, if necessary). but i think you're confusing
primary keys and foreign keys here. the CompanyID field is a primary key
in
the Companies table, but would be a *foreign* key in the CompanyProcess
table. suggest you read up on tables/relationships (more, if you've
already
done some reading); it's important that you understand the basics of
relational design principles so you can set your tables/relationships up
correctly before you go further. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

Also, if you can have more than one Contact per company, clearly CompanyID
cannot be the PK in that table, since if it is each CompanyID could only
appear once in that table.

HTH;

Amy

 

[justin.peskar]

Sorry I used the wrong language. I guess what I ment to ask is if it
is common to use two fields for the primary key. What are the possible
implications of doing so?

Thanks

Justin

 

[justin.peskar]

Also, if you can have more than one Contact per company, clearly CompanyID
cannot be the PK in that table, since if it is each CompanyID could only
appear once in that table.

Oh correct. Sorry the Contact ID is the PK and the Company ID is the
FK. I typed that out wrong. Is there a way to include screen shots?
That would make life Sweet!

 

[tina]

Access supports multi-field primary keys, and "natural" keys are often
composed of multiple fields. usually, a multi-field key is made up of fields
with "real" data in them, and as a result, the data in one or more key
fields may need to be changed at some point. if the primary key is used as a
foreign key in another table, you need to enable CascadeUpdates in your
tables relationship to support changing key data, in order to maintain the
link between parent and child records. this can be an issue in multi-user
databases, where a user is editing a record when a cascade update attempts
to change that same record. there may be other ramifications that don't come
to my mind readily, as well. i personally have very little experience with
those issues, because i rarely use a multi-field primary key - and *never*
when that key will be used as a foreign key in another table.

many skilled developers do use multi-fields primary/foreign keys, though.
suggest you read up on the issue, and base your decision on more, varied
information than what i'm giving you here.

hth