Problem identifying tables

[hmushtaq]

I am designing a database for the ticketing department of our company. Along with dozens of other tables, I have 2 tables namely, Contracted Airlines and Contracted Agencies. I would like to store the scanned copies of the contracts that we do with both the airlines and the agencies. The other attributes I would like to include along with the Scanned contract copy are: Contract Starting Date, Contract Ending Date and Contract Status (Expired, Renewed).

The question I am posed with at this stage is whether I make two separate Contracts tables (one for each), or do I make one Contracts table and link them to the Contracted Agencies and Contracted Airlines tables? I am very much inclined to go with the latter, since there are common attributes between the two tables and repeating them doesn't seem to be wise, not at least to me (not to mention I might be wrong), and include another field to the common Contracts table called "Contract For", where the values can be "Agency", and "Airline", storing all the contracts in one place.

I have one more table where names of all the airlines have been stored called "Airlines". This is my validation table from where I pick up the airlines to store into the Contracted Airlines table.

Now while storing the contract copies, I would have to select the agency name or the airline name corresponding to the record storing the contract copy. This is where I am confused as to how will I be able to store the name of the agency or the airlines within the same field using lookup lists from two different tables? (as I can only hook it up with one of the tables at one time).

I know I can acheive this by using VBA on the form, and changing the rowsource of the combo box at run time to the corresponding table, but is it going to be a wise decision considering the design of the overall database, or it can in future bite me under any circumstances?

What if I design something like the one shown in the attached image? I have used two more linking tables but I don't know if it makes sense at all by making Contracts a parent table rather than a child of two parents namely, Contracted Agencies and Contracted Airlines.

Need your honest opinion on this one. Thanks

 

[jdraw]

Do you have test data? Do you have a list of Business rules for your Business? What's the difference between Airline and Agency in your Business?

Try you sample data against your proposed structure according to the Business rules. Identify any/every anomaly and reconcile the anomaly as
a) bad test data
b) error in data model
c) other

Make necessary changes and retest everything until your model supports the business rules.

Good luck with your project.


Don't cross post without advising the reader.
http://www.utteraccess.com/forum/Problem-Identifying-Table-t2009527.html
http://www.accessforums.net/database-design/problem-identifying-tables-38087.html

Here's why
http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

 

[The_Doc_Man]

While I don't know where you are going with your design, I can suggest that if you are worried about validating from two different tables that have some similarities, you might do better to validate against a UNION query that selects only the similar fields and use THAT for a single-source validation.

 

[hmushtaq]

Thanks jdraw.

I have the test data along with the business rules.

The "Agency" is any organization that fascilitates us in issuing tickets while "Airline" is any organization that provides us the means of transportation. We deal with them separately based on the individual contracts we do with them.

 

[hmushtaq]

Thanks The_Doc_Man.

I thought about the Union queries. But then I am worried if it is going to be a good design practice. Or do you recommend me separating the two types of contracts not to overcomplicate it for development? I am concerned about the ease of use also.

Do you think my proposed design could work? What do you need to know about the design? I can provide more details in my other post.

 

[hmushtaq]

I would like to sincerely apologize to all the readers for not knowing the etiquettes of posting, as I don't use the forums very often. But I do know now and will adhere to the rules of "cross posting". Due to the short deadline to finish this project, I got desperate to get some answers to my problems, but I was wrong in doing so. I truly respect the time and effort people are putting in here voluntarily and I highly appreciate it. Please accept my apology.

Note: I tried to declare the links to my cross posts but I am getting the following error:

"To be able to post links or images your post count must be 10 or greater. You currently have 6 posts.
Please remove links from your message, then you will be able to submit your post."