Table Relationship Verification Request (1 Viewer)

Hello,

I have a database that tracks projects and quarterly reports for those projects. Each report I create has a set number of transactions associated to it. I have attached a picture of the current relationships and just wanted to get some feedback on whether I have done it correctly.

I am specifically curious about tblProjectTransactionBridge, as I used a structure that came from an invoicing system, so I am not really sure what this table is doing, but so far it seems to work.

Hopefully the image is straight forward enough to give you an idea of what I'm trying to accomplish.

Thanks for the feedback.

That's a lot of _ID fields. Seems you've left out a bunch of tables--which is fine, but they are leading me to some assumptions.

1. Do any permutation of those _ID fields share a relationship outside the tables you have shown? For example in tblProject, does ContactID and CompanyID have a relationship, like does a contact belong to a company? If so, this relationship shouldn't be in tblProjects, just the lowest level ID of that relationship should be there. Again, that's just an example. IF TRLevelID and BRLevelID are in a relationship outside tblProject the same applies.

2. Redundant data. I see CompanyID, CompanyStatus, CompanySize fields in tblProjects. Do you have a table called Companies and does it have those 3 fields as well? If so, you only need to store CompanyID in tblProject and you then link to Companies to get that other data about the Company, you don't store data in multiple places.

3. I smell overnomralization. Tell me about EventCityID, does it link to a table with just 2 fields--an autonumber ID and then a text field that holds the City? If so, that table should go. You simple store the actual text value in tblProject instead of EventCityID. Any table with just 1 real field (autonumber IDs don't count) shouldn't exist. You should instead store the related value in the target table instead of the ID. I have this question about TAPEventCountryID and TAPEventProvinceID. And all the _ID fields as a matter of fact.

4. TransactionTypeID seems to be in the wrong table. Again, just an assumption, but going on name alone I would think TransactionTypeID would be better put in tblProjectTransactions.

And to satisfy your concern about tblProjectTransactionBridge--that looks good. When you want to make a many-to-many connection (like between tblProjectReport and tblProjectTransactions) you use a junction table to sort out that relationship.

Thanks for the reply plog. To answer your questions:

1. Most of those ID fields have a one-to-many relationship with a single lookup table that contain attributes for each project. The reason ContactID is in tblProject is because we would like to associate any contact person to any project and not be constrained by the company that they may work for. I will review this structure and make sure it is correct, but it has been working so far.

2. Ya...the company status and company size fields were a tricky one. We track the size of a company in the company table, as well as in the project table because we want to know the size of the company when their project started...not their current size. I realize that it isn't normalized, but couldn't think of any other way to do that.

3. I see your point here and somewhat agree. The problem is that we need to run queries to calculate our company metrics and if I allowed the users to just type something in text, the database would end up with spelling mistakes, which in turn would end up screwing up my counts. To illustrate this, I will use ResourceSectorID as an example.

Lets say project 1 is in resource sector Forestry

Project 2 is in resource sector Foerstry

My final count of projects in forestry would be 1 (incorrect). to avoid this, I use lookup tables. My real problem with this method is that I can only have so many relationships with referential integrity enforced before I need to upgrade to something like a SQL server.

4. Interesting... I think I had it that way earlier but then changed my mind. Currently we have 4 report types and based on the report you choose, I filter the transaction types. So if you had reportID # 1 , you could have TransactionTypeID 1,2 or 3 ... for example. I dont really need to know the transaction type in the transaction table because I can just get it from the project report table.

Come to think of it...im not really sure I even need the report type, as that might be redundant. I could probably get away with just the transaction type.

Regarding the forestry issue, you can still limit their options without using a whole new table. In Design View of the table you go to the field you want to limit, then at the bottom you click on the 'Lookup' tab and change the Display Control to Combo Box.

At this point you can feed your field data in 2 ways:

1. Select 'Value List' in the Row Source Control and you can input your list semi-colon seperated. e.g. 'Forestry', 'Finance', 'Farming', etc..

2. If your table is populated and records exist that hold every possible value, then you can select 'Table/Query' in Row Source Control and then build a query that looks back into that table and field and selects the Distinct values already in it.

That's how I recommend handling that situation--and all others where you have a table with just 1 real field.

I would suggest reading this before switching to lookup fields: http://access.mvps.org/access/lookupfields.htm

Personally I feel the drawbacks outweigh the advantages.

plog said:

Regarding the forestry issue, you can still limit their options without using a whole new table. In Design View of the table you go to the field you want to limit, then at the bottom you click on the 'Lookup' tab and change the Display Control to Combo Box.

At this point you can feed your field data in 2 ways:

1. Select 'Value List' in the Row Source Control and you can input your list semi-colon seperated. e.g. 'Forestry', 'Finance', 'Farming', etc..

2. If your table is populated and records exist that hold every possible value, then you can select 'Table/Query' in Row Source Control and then build a query that looks back into that table and field and selects the Distinct values already in it.

That's how I recommend handling that situation--and all others where you have a table with just 1 real field.

Click to expand...

The reason I chose not to do this is because if we ever change something, I will have to make changes in many different forms, rather than just in a table. It is something I will keep in mind if I ever run into the maximum allowable relationships problem though.

I do like the tricky method in your second suggestion but I don't think it would work just yet.

Frothingslosh said:

I would suggest reading this before switching to lookup fields: http://access.mvps.org/access/lookupfields.htm

Personally I feel the drawbacks outweigh the advantages.

Click to expand...

I definitely won't be using lookup fields in my tables.