CODEWITHSUNDEEP
ms-access
lil_bugga
lil_bugga

Reputation: 91

Combined Field Primary Key in MS Access?

I'm looking to create a database system that help keep projects I'm working on organised. My aim is to create a table for each project that I do, from which I can find out who I've dealt with and what I've done for that project.

The way I see it is that project can contain multiple pieces of content and that content can be submitted by multiple clients, but one client isn't restrcited to working on just one project.

I'll include a picture of my proposed table designs now as things could get complicated.

proposed tables for database

In MS Access I was able to open the projects table, click on the + to see who worked on that project (clients table), then click on another + to see the work each user submitted (content table). Eg.

Projects Table: id 1, project #1, client #1
    + Clients Table: id 1, client #1, user #1
            + Content Table id 1, Project #1, Logo Design, Client #1
                            id 2, Project #1, Create Website, Client #1
      Clients Table: id 2, client #2, user #2
                     id 3, client #3, user #3

When I went to add the third project associated to the second client it told me there was data duplication, or something along those lines.

I feel that the soloution is to link the project name and the client name together in the projects table, that way if I can have 2 or more clients associated with one project but prevent assigning a client to the same project twice. It should also allow me to assign one client to multiple projects.

As long as there is no instance of the same project name and client name it should accept the data

So my question is, is my soloution the right one and if so how do I impliement it? Am I even going about things the right way?

I hope what I've typed makes some sence to someone and that someone can offer me some help/advice with this.

Cheers Ben

Upvotes: 1

Views: 1850

Answers (1)

HansUp
HansUp

Reputation: 97101

It sounds like you're describing a many-to-many relationship between projects and clients. In that case, the standard approach is to have a table for projects, another for clients, and then a junction table which holds the pairings of each project/client combination.

Add a unique constraint (index) on project plus client in the junction table so that it will accept only one row for the same project/client pair. You could use a composite primary key to satisfy the unique constraint.

Upvotes: 1

Related Questions