Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

trouble with tables 1

Status
Not open for further replies.
jreynold

jreynold

Technical User
Dec 20, 2006
24
US
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
You may want to google "creating Many to Many relationship" and look at other examples. Here is one.

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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Saved Imoorts problem with file name
Replies
10
Views
801
Andrzejek
Andrzejek
JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
939
SkipVought
S
MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
v5652
  • Locked
  • Question
Trouble opening database Make sure the drive is available
Replies
2
Views
641
v5652
v5652
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242

Part and Inventory Search

Sponsor

Back
Top