trouble with tables
trouble with tables
(OP)
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.
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.
RE: trouble with tables
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
RE: trouble with tables
RE: trouble with tables
http
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.