Composite Primary Key

[shreyans.ghia]

Hello,

This is my first post on this forum. Hence in case i have missed out on guideline re posting messages, excuse me for the same.

I needed some help re Composite Primary Key in Ms Access 2010. I shall try and explain my tables in detail. So my first table is "Client Database" where the field "Buyer" is primary key. This is connected to my next table "Project Database" which has fields such as Work Order #, PO #, PO Date , Buyer etc where Work Order # is the primary key. Finally "Projects Database" is connected to Order information. Now the problem is until now, I always had 1 PO # for each Work Order #. However now I am facing a situation where my client has clubbed multiple PO #'s for the same Work Order #.

From what I understand to solve the above issue I need to trigger the Primary Key Violation only if both the Work Order # and the PO # are the same.

Hence any help on the above would be highly appreciable.

Thanks in advance.

Shreyans.

 

[Galaxiom]

Open the Index window on the table. Add another line for WorkOrder directly under the PO field.

BTW You should definitely remove spaces and special characters from your names. They only cause trouble, especially the hash.

 

[Solo712]

Hello,

This is my first post on this forum. Hence in case i have missed out on guideline re posting messages, excuse me for the same.

I needed some help re Composite Primary Key in Ms Access 2010. I shall try and explain my tables in detail. So my first table is "Client Database" where the field "Buyer" is primary key. This is connected to my next table "Project Database" which has fields such as Work Order #, PO #, PO Date , Buyer etc where Work Order # is the primary key. Finally "Projects Database" is connected to Order information. Now the problem is until now, I always had 1 PO # for each Work Order #. However now I am facing a situation where my client has clubbed multiple PO #'s for the same Work Order #.

From what I understand to solve the above issue I need to trigger the Primary Key Violation only if both the Work Order # and the PO # are the same.

Hence any help on the above would be highly appreciable.

Thanks in advance.

Shreyans.

I think you need to tweak your design a bit to normalize the tables. The Project table (with Work Order # as the PK) should not contain any fields relating to POs. Clearly, from what you say, it would be the POs that have Work Order as foreign key, as you indicate one-to-many relationship exist between them. You can then have as many Purchase Order #'s relating to a single Work Order # as you want. You do not need a composite primary key in either of the tables.

Best,
Jiri

 

[jdraw]

I agree with the others that some research and practice with Normalization would help you. I recommend that you pick a naming convention that does not allow embedded spaces or special characters as Galaxiom has advised.

Here is a link to a tutorial on table design - it includes Normalization and establishing relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

I agree with Jiri that a composite PK doesn't seem warranted.

 

[shreyans.ghia]

Dear all,

Thank you for the response. Well I had thought earlier that I could avoid having the Composite Primary Key function by excluding the PO details from the "Projects Database" table. However this option wasn't working best for me since I have other reports / forms / excel files linked with this table and I would have to alter the entire database.

Hence I was hoping to understand how we create the Composite Primary Key. I was unable to understand the suggestion as given by Galaxiom. Would it be possible to give your answer in little more detail.

Thanks.

Shreyans.

 

[Galaxiom]

Open the table in design view.

Click Indexes button in the Design/Tabletools Ribbon.

That table will have an entry something like this:

PrimaryKey | PO# | Ascending

Add another line directly under it so that it looks something like this:

PrimaryKey | PO# | Ascending
           | WO# | Ascending

These two lines are part of the same index because the second row has no entry in the Index Name column.

 

[gemma-the-husky]

Dear all,

Thank you for the response. Well I had thought earlier that I could avoid having the Composite Primary Key function by excluding the PO details from the "Projects Database" table. However this option wasn't working best for me since I have other reports / forms / excel files linked with this table and I would have to alter the entire database.

Hence I was hoping to understand how we create the Composite Primary Key. I was unable to understand the suggestion as given by Galaxiom. Would it be possible to give your answer in little more detail.

Thanks.

Shreyans.

This is quite a long reply, and I hope you find it useful.

first, you just cannot decide to avoid things arbitrarily. your logical data structure needs to follow the real world structure you are modelling, which will lead you to a certain data structure.

When analysing data, don't forget that Users and Specifiers will use a shorthand. They will talk about the Purchase Order, but what they really mean is a POLINE. A PO will normally consist of multiple lines. The PO (Header) will contain data relevant to the whole order - order number, supplier, order date, but other data will be contained in the POLines.

There will also be complex considerations involving things such as different delivery dates per order line - or even scheduled order delivery over time. All of these considerations will need to be taken into account.


so you may naturally gravitate to a structure such as this

POHeader - overall details of the Purchase Order
POLines - (1..m relationship)

WOHeader - overall details of the works order
WOLines (1.. many relationship)

now potentially you may also need a further table
WODetail (a junction table between POLines and WOLines)

the final consideration is the relationship between the the polines and the wolines. clearly a poline may need to be split into multiple production batches, depending on its size - therefore polines...wolines is at least 1 to many. it depends whether the situation is reversed - can a a single woline combine multiple polines.

----
so the important analysis phase is for you to consider every part of the system to be modelled, and ensure you understand all the nuances. in many cases the factory work will be standard, but there may be a few special cases that have to be managed differently. The dbs has to be desigined for the special cases.

for example, if there is just one large order a year that needs splitting into multiple production batches, then the system needs to be built with this in mind. It won't matter that 99% of jobs do not use this facility. Your dbs will still work anyway. It will matter though, if you do not provide the facility when you get the one job that does require such a split.


Now the actual nature of the keys is not so important. in the above example, the key to the the PO will be the supplier plus the POnumber. So in the POLines table, this composite pair needs to be used as a foreign key to enable the tables to be related correctly. In many cases, dbs designers will add an autonumber key to the the PO table, and use this as the foreign key in the POLines table - instead of the foreign key. it is often easier to manipulate a single key than a composite key - but the structure is identical.

Anyway, I hope you find this useful.

 

[shreyans.ghia]

Well Galaxiom - well when did the changes you suggested, it broke the relationship which I had between "Projects Database" and the Query "Order Information". Is there any solution to this as well?

Gemma - thanks a lot for the detailed reply. I think it might be difficult to get this sorted given the way i have structured my tables. Probably I might have to re-work on them.

 

[Galaxiom]

Well Galaxiom - well when did the changes you suggested, it broke the relationship which I had between "Projects Database" and the Query "Order Information". Is there any solution to this as well?

I didn't actually suggest any change. I simply explained you what you asked, how to create a composite primary key.

Of course any relationships based on the key would also have to be changed to use the two fields. Queries would also need to be adjusted to join on both fields too.

However I have not analysed whether this change is really what you need. I have not read the other posts in detail but they all seem to suggest that it might not be the solution.

Making changes to a data model so far into the design is often fraught with problems. This is why it is so vitally important to define the buisness model right from the start.