Table relations without a primary key?

[Sly600rr]

Hello,

I am very new to access, and don’t have any kind of training other than this forum and googling various things, I think I’m having a hard time wrapping my head around tables and relationships, how to set them up, and how to put into and pull information from them correctly.

I am making a database for work (manufacturing), Its tracking orders, going through our quality department, how long inspectors are working on them, and how many, if any; Non-conformances (NCs) they find.

My main problem at the moment is trying to find a way to relate the NCs to the orders. Since there can be multiple NCs Per order, I can’t think of a way to setup a Foreign Key. So I have a relation setup between the two using the SWO# but with no Primary key on my NC Table.

Can you do this? Or will it give me problems in the future? What would be the right way to relate the two tables?

I attached an screenshot of my database table relationships.
Any help would be appreciated,

 

[lfarnsworth]

That should work. If you do a query by SWO#, for each SWO# you will retrieve multiple NCs, which I imagine is what you want.

I would recommend giving each NC a unqiue key so that, if you need to, you can use it to differentiate between them.

Otherwise, your setup looks OK to me (for whatever that's worth ).

 

[David Eagar]

Have you looked at the Northwind samples? There are a couple of order tracking models there that will give you a much clearer idea of how to structure your tables and create the 1 to many links needed

 

[boblarson]

As I see it, based on you diagram, you are missing an Order Number Primary Key and then you would have your NonConformances table use that as the Foreign Key.

 

[Sly600rr]

My problem is, I have multiple Nonconformances for each order, so I would need duplicate foreign keys to relate to the orders primary key, which it wont let me do.

 

[gemma-the-husky]

My main problem at the moment is trying to find a way to relate the NCs to the orders. Since there can be multiple NCs Per order, I can’t think of a way to setup a Foreign Key. So I have a relation setup between the two using the SWO# but with no Primary key on my NC Table.

My problem is, I have multiple Nonconformances for each order, so I would need duplicate foreign keys to relate to the orders primary key, which it wont let me do.

not quite

you have an order table
you have a NC table

in the NC table, you have a link to the order table (ie the order number)

now normally you would have something ELSE in the NC table as well as the orderid to distinguish the failures - say an NC code (it needs to be something that would make the NC unique - if you can only have one NC of each type, for each order this is Ok, otherwise you need something else) - you dont HAVE to be able to distinguish beteen the NC instances for each order, but if you cant it may give you problems.

so your tables are therefore

order (orderid (PK), customer, date, status, any other info relating to the ORDER)
NC (orderid, NCType, date, details, etc, any other info relating to the NC)
{in this scenario the orderid in the NC table is a foreign key, and there will/may be multiple instances of this for each orderid}

{in this scenario the primary key in the NC table will consist of BOTH fields orderid AND NCType - some users might add an autonumber field as the PK - in which case you may or may not decide to add the orderid-nctype as an additional index/key (the terms are synonymous) - its a matter of taste whether to add the autonumber field, its just that primary keys based on single fields are generally easier to handle}

the tables then link the orders to the NCs in a one to many link ie one order can have multiple NCs. As designer its your job to store data elements in the correct table, and design a normalised structure that follows this paradigm, so that data elements (fields) are stored once and once only

having done this, when you have an order, access will automatically retrieve ALL the associated NC's (if any)

i hope that makes it more clear

--------------------------

the main idea is that you cant have many to many relationships (access just can't model them properly) - you have to analyse all your data into 1 to many [1-many] relationships (occasionally 1 to 1 [1-1], but thats just a special case), for relational databases to work

 

[Sly600rr]

Thanks for your reply Husky, But I guess I still dont quite uderstand..

Are you saying that my relationship is okay? without the primary key?

I do have an NC Code feild too, it just wasn't shown on that model

I have an updated database model if you dont mind taking a look at and telling me if I'm on the right track.

I mean, the relation works, I can find the NCs for the orders when I search, I just didn't know if this was the right way to do it without using primary keys.

 

[gemma-the-husky]

seems ok - do you not have a primary key, in your table tblNCData

anyway you have it set up as i outlined

your table tblNCData is linked to tblOrders, via SWOOrder
in addition you have NCCodeId identifying the type of NC Instance

Now if there can only be 1 instance of a given NCCodeID per Order then you could add a key (or primary key) consisting of both these fields. If one order could have say 2 type 1failures, and 1 type 2 failure, then you cant have this as a primary key, because a primary key prevents duplicates. Access will warn you if you have duplicates, and wont create the key

(to do this it would be easier to drag up NCCodeID to be the second item in the table design - this is just moving the position of the field - it will cause no other problem - then higlight both these fields and click as primary key.)

 

[Sly600rr]

Yes, unfortunatly there can be multiple NCcodes per order, so then your saying its okay not to have a primary key in a table if you dont have any unique keys?

 

[gemma-the-husky]

it depends on what you want to do

lets say your NC types are

type 1 - incorrect assembly
type 2 - casing damaged
type 3 - missing screw
type 4 - colour problem

if there can only be one of each type of these per order, then no problem
if there can be two of a particular type of NC for a single order, then can you distinguish between the two instances

now having a complete duplicate MAY be a possibillty, but more often than not, there will be some other information enabling you to distingusih between the otherwise identical NCs

so if want to retrieve a particular NC instance, to record the fact that it has been attented to, say, you have to be able to uniquely identify it,(ie you need a unique key) otherwise access will retrieve the first of the duplicates it finds, and you will have no control over which one this is.

so if you DO have a unique set of fields, then you have a candidate primary key.

hope this helps

 

[Pat Hartman]

As has already been mentioned (in different words), the relationship between orders and SWO is backwards. Remove the SWONumber from orders and put PartNumber in the NC table. I would also add an autonumber pk to the NC table so that it has a primary key.

Then go back into each relationship and select the enforce RI option. However, you need to think about each relationship to determine if you need to set the cascade update and/or cascade delete options. the cascade update is an invaluable but some think dangerous, setting. In a typical order entry application, the relationship between customer and orders should NOT specify cascade delete. You need to control this deletion programatically. However the relationship between orders and orderdetails, should specify cascade delete. That way, when you delete an order, its details are deleted automatically.