Problem identifying tables

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

My overall response is that putting the contracts in a single table will overcomplicate your life - for instance, by making certain queries non-updateable. I wouldn't do it, unless there was a case where an agency and an airline were on a single contract. If you do put them in a single table, then your sanest option would be to deNormalize that table by including the most common fields (Name, etc) from the two tables.

What types of information do the Airline Contracts and Agency Contracts Tables contain? Is this name and address info? Could there be a need for a child table in the case where you would list a corporate address and then associated field offices?

Wondering about your proposal. There could be benefits to the user, probably not the developer though. Operations may need to use a different form (interface) for the two types Airline vs. Agency. When it gets to the back office, having it in one table could make those users' lives easier.

How large is this organization and how large could it get?

Thanks Jeanis.
That's what I was thinking. But then in terms of expandabillity, do you think it will be a wise option? What if we have to add other types of contracts in future?

Thanks ItsMe.

Based on my proposal, I was thinking of storing the airline name (picking up from the table below, from "Airlines") and a foreign key to the "Contracts" table. We don't require any address information or branch information.

We have about 2000 employees on-board. The growth of the organization in terms of manpower is steady, so I wouldn't worry about that.

That's what my concern is to keep it easy on the users while also making sure that I design it properly.

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. Following are the links to my cross posts:

http://www.utteraccess.com/forum/Pro...3#entry2401203
http://www.access-programmers.co.uk/...67#post1296267

Looking at some of he other threads it seems as though there are some major differences between Airline Contracts and Agency Contracts. I would further analyze these differences. Once most differences are identified I would investigate how the users deal with nuances in operations compared to back office.

You are going to have to way the pros and cons. If the differences are so extreme and the business rules cause many extra steps, a separate table to store those nuances/differences/business rules. Sounds far fetched, but it may fit into one table. You could have a tblNuances. Obviously you would want another name but....

I would keep in mind the expansion capabilities, complexity of coding the user's interface, keeping the DB matrix intuitive when looking from an outsider's perspective.

Well there aren't any major differences. They are the linking tables I had to come up with in order to store only those airlines and only those agencies with whom we have contracted, without having to store these directly into the Contracts table, since in the latter case they both will have to be stored in a single field, which is what I am trying to avoid.

What I had in my mind was something like the following:

Contracts Table:
ContractID
ContractCopy
ContractStartingDate
ContractEndingDate
ContractStatus (I will not include it in the table, since I have decided to make it a calculated field, but I am mentioning it here just to give you the overall idea of the contracts table)

Airline Contracts:
AirlineContractID
ContractID
AirlineID (FK)

Agency Contracts:
AgencyContractID
ContractID
AgencyID (FK)

So I don't know if this model could actually work.

NOTE: I am not trying to cross-post any more in order to get answers, but only trying to clarify ItsMe's reply which I didn't earlier. Again my apologies for any misconduct of mine.

I think that if you test that model you are going to find you will need to bring more columns than you have listed here into tables Airline Contracts and Agency Contracts. These columns will be similar in nature and name.

I have situations like that where I put it into one table. Some times as many as four columns will remain null when one or the other option is being recorded. I use a yes no field to differentiate. In one instance I did it after the DB was live for a couple of years. Either way it is a lot of coding and managing referential integrity in your own matrix.

You won't know until you test it in the lab and interview the users which is the lessor of the two evils.

Have to agree with ItsMe on all counts. Real life usage doesn't generally conform to the dictates of best-practices, so data base design is more often a craft, rather than a science. It's very usage-specific which way is "better".

I agree you'll probably have several more fields in each of those tables. I do think that your use of linking tables will simplify your life somewhat, since an INNER JOIN to the AirlineContract linking table will omit all the Agency contracts and vice versa. That feature is elegant.