Composite Key relationship (1 Viewer)

jaryszek

Registered User.
Local time
Yesterday, 19:32
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have 2 tables and want to relate them.



tblOsServers is coming from Excel.

Generally i do not have primary key but i created composite key in tblOperatingSystems on fields: Publisher, Sku, Offer as unique.

And now i have table tblOsServers.
Generally one OperatingSystem can have many ServersID.

And now i have to relate this tables somehow. And in this field i need your help. How can i achieve this?

Please help,
Jacek
 

Attachments

  • Screenshot_16.png
    Screenshot_16.png
    39.3 KB · Views: 404
  • Database231.accdb
    400 KB · Views: 85

Minty

AWF VIP
Local time
Today, 02:32
Joined
Jul 26, 2013
Messages
10,355
I think you need a third junction table - tblServer_OpSystems

BUT - I'm confused by the Server ID in tblOsServers is that not unique?

In fact, I'm generally confused by the naming of the tables and fields, and the lack of a single PK in the OS table.
An OS is an OS. If you are storing a price point/order of it that belongs in another table.

You would then save that and your server OS junction would simply refer back to the offer table?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:32
Joined
Oct 29, 2018
Messages
21,358
Hi. When you say tblOsServers is coming from Excel, are you saying it's a linked table?
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:32
Joined
Jan 23, 2006
Messages
15,364
Jacek,

Please provide a little more description to help readers understand your post/requirement.
In most scenarios you have a business issue/problem/opportunity that you can describe in "business terms". And from that description you can design tables and relationships that you can test and adjust with sample data.
Your comment Generally i do not have primary key... is a little suspect, but maybe a broader description would clarify.
Good luck.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:32
Joined
Feb 19, 2002
Messages
42,981
I modified the database to make this work.
1. I switched the order of Other and SKU in the services table to keep the lines from crossing. This was only for visual neatness. You don't need to do it if you don't want to.
2. I added a unique index on the four fields in Services that define uniqueness. I left the autonumber as the PK but you could remove the autonumber entirely and just let the PK be the four fields
3. I changed the data in the OS file to make it match what was in the Services table. This was necessary. You cannot enforce RI if the data doesn't match.



If the spreadsheet has a unique identifier, that would be the best thing to use. If it doesn't, you're stuck with the 4-field PK. I would not use an autonumber on this table.
 

Attachments

  • Database231_Pat.accdb
    456 KB · Views: 78
  • RelationshipOnMultipleColumns.JPG
    RelationshipOnMultipleColumns.JPG
    28.3 KB · Views: 188

jaryszek

Registered User.
Local time
Yesterday, 19:32
Joined
Aug 25, 2016
Messages
756
Hi Guys,

sorry for my late answer.
Wow i am grateful for providing such a nice solution.

And explanation. I will implement this now and try...
Let know you if is working for me.

Best,
Jacek
 

Users who are viewing this thread

Top Bottom