trouble with tables 1

[jreynold]

I am rebuilding a database for my small construction company. I did the initial design of it but it has been a while since I have worked on it.

I am having trouble wrapping my head around the following issue. I've read and reread the article by Paul Litwin, "Fundamentals of Relational Database Design" but I guess I still dont' quite get what to do in this situation.

I want to be able to keep track of all the companies that work on a particular project. (Owner, Engineer, Architect, General Contractor, Mechanical Contractor, etc..)

One of these companies will be our client.

My trouble comes when one company fills two roles on the project (for example be both the engineer and the architect).

I'm sure this has to be common and I'm just being dense, but if anyone could explain I'd appreciate it.

 

[MajP]

Without seeing what you have so far it is difficult. But maybe

TblCompanies
companyID
companyName
other company fields

tblProject
projectID
projectName
other project fields

tblRoles
roleID
roleName

example for tblRoles
1 Engineer
2 Architect
3 Inspector

now a project has roles this is a many to many relationship

tblProject_Company_Roles
companyID
projectID
roleID


example
1 1 1
1 1 2
2 1 1
1 2 1

that says on
Company 1, on project 1 is Engineer
Company 1 on project 1 is also architect
Company 2 on project 1 is also an engineer
Company 1 on project 2 is an engineer


A query can tie all that info together

 

[jreynold]

Sorry for late response, I have not had time to work on the database since I posted. I pretty much can only work on it in spare time which is hard to find these days. I will try to implement your idea soon and post back with status. Thank you so much!

 

[MajP]

You may want to google "creating Many to Many relationship" and look at other examples. Here is one.

http://www.databasedev.co.uk/many_to_many_example.html

Once you understand how to create tables that can do this, the next step is understanding how to create user interfaces to work with a many to many, and how to query them to bring it all together. It is not super complicated, but it is one of those things you have to see first, and then it makes sense.