Table Design / Relationship Help

[tmyers]

In an application I have been working on, the further into it I get, the more concerned I am that my basic table structure and foundation is built incorrectly. A stripped down version of the application (only the tables remain) is attached.

This is for an electrical construction quoting. Light fixtures in particular.

I will try to lay out the path and method to my madness so people can hopefully understand what I am trying to accomplish.

QuoteDetails is my "primary" table. It houses all of the pertinent info related to a job (quote #, bid date etc).
QuoteDetails is then linked to the table Revision (quotes could multiple version/history that I need to track).

Those two were easy, the rest is where I am running into problems. Also please note that some tables are not fully fleshed out yet (such as Status and Vendors)

Revision USED to just be tied to Type, but I ran into issues later when trying to implement the table Sheets. A job consist of Sheets (construction plans), within those sheets are types (lighting fixtures designations), with each type having a count on said sheet. In the attached, I tried adding it in differently, but don't believe it is correct at all.

My table Notes is just to hold, you guessed it, notes about a type. NEMA ratings, special features/options etc.

The other tables for the purpose of this question can be ignored.

Is there a better / more correct way to structure the tables? A quote can consist of multiple Revisions (minimum of 1 for the base/original quote). A Revision consist of multiple Sheets. Sheets can have multiple types, and a single type can be on multiple sheets.

I know more clarification will probably be needed, so ask away!

 

[plog]

There's a lot of easy to see, common normalization errors in laying out tables that you have not committed. At the individual level, each table looks good. The problem arises with your relationships. In a properly structured database there should only be 1 way to trace a path between 2 tables. You have a spider web of relationships allowing multiple ways between tables. That's the big issue I see with your database.

For example, In the Relationship Tool I can go from QuoteDetails through Users to Contractors, or I can get to Contractors via QuoteContractor. Only one of those is right, and we don't know enough about your data to know which it is. Sometimes this means removing fields from one table and placing them in another.

My suggestion is to remove all your tables from the Relationship Tool except QuoteDetails. Then add tables back one at a time, making sure they only relate to just one existing table. When you come to a table you want to connect to multiple existing tables, its time to evaluate that table because relating it to multiple existing tables is incorrect.

 

[tmyers]

@plog In my process of learning Access, I learned about left/right/inner/outer joins. While I know there is a "connection" between tables that shows multiple paths to other tables, I thought it was OK as long as your joins were correct as to not allow data to "flow" a certain direction.

Did I misunderstand the idea behind the joins?

 

[tmyers]

As always, thanks for the insight Pat!
I think the problem with my tables is I broke them apart too much. There are more than likely several instances where a few of them can be combined into one, such as the products and types you mentioned.

Sheet Count was supposed to be the table that held the quantities for the sheets. Per above, I think that table can be eliminated and rolled into another table.

Looking at your table structure, that give's very good insight into what I need to do. For my own curiosity however, was I correct about the type of joins I was using in that while there are multiple paths, the type of join prevents circles? It was my understanding that certain join types only allow data to go one direction, preventing circles.

 

[tmyers]

I made some adjustments.

I deleted the tables you mentioned, but will have to do something with the removed ContractorCounts table, as that was a table meant to store counts we received from the contractor(s) for a job. A job can have multiple sets of counts (whoever is doing the job inhouse, and several contractors bidding could also send in their counts, each of which could be different).

I renamed Sheets to Drawings (that name makes more sense in the long run) and rolled SheetCounts into it. So now the table Drawings has a quantity field. Removed the # from all names per your suggestion.

Should I change how type and products are related? I currently have them related through the Notes table. Should I just remove the Notes table, roll that into Types, then relate product to it? (I honestly had not gotten far enough to deal with products just yet).

 

[tmyers]

Attached is the revised structure.

 

[tmyers]

Fair enough. All the "Count" specific tables have been removed and rolled into other tables.

Counts are quite literally counts. A sheet/drawing/blueprint will show "x" amount of a light fixture's (each fixture has a type designation) and we literally hand count them on each drawing.

Here is an example:
Construction drawings for building A.
Page E-101 show first floor layout.
We count each kind of fixture (each unique fixture has a type designation). Lets say there is just one type on this page.
So if fixture 1 has a designation of type R1, and there is 4 of them on this page, we have to record (4) R1's on document E-101.
Page E-102 has 2 fixture types, R1 (again) and R2.
On this page, there is only (2) R1's but (22) R2's. Those specific counts would have to go under E-102.

At the end I would have a data sheet that shows the total for each specific type counted across all entered sheets.

Does that help clarify it a little bit?
I am not the best at detailing my thoughts sometimes.

 

[tmyers]

Would you recommend that I break some of the details out of the "JobDetails" table? I currently have all the job info in that table (quote number, job name, bid date etc). It is what I call my "primary" table, where everything quote unquote starts.

 

[tmyers]

Type is purely a designation. A construction document will have a fixture schedule (a list) of all the products that are approved for the job. Each of those fixtures have designation assigned to them. So fixture A has a type R1, where as fixture B has a type of R2 etc.

One specific fixture/product code could have different type designations from job to job. There isn't any consistency from job to on types. One job could be simple R1-R20. Another job could be XXR, JRS5, LKJ6 etc. Likewise, one type could have many products. R1 could be one of 5 different manufacturers, whereas R2 could be strictly one manufacturer. The next job could flip that. One other issue is what is known as "linear" fixtures (fixtures that can be various lengths). One jobs R1 is a simple 2X2. The next jobs R1 is a linear which could have 5 different lengths under that same type. R1-2', R1-4', R1-12' etc.

An argument could be made that Type and Product are one and the same. The reason I went with two tables was due to how we currently do our jobs. One of the first things we do when starting a new job, is enter all types from the schedule. Also do to the end of the previous explanation with how a single type could have many different products. We also don't know the products catalog number generally until around the end of the job quoting process.

I attached an example from a job I am actually working on. This is a small one. On a large job, this schedule/list could be several pages long. This might better show what I mean.

 

[mike60smart]

OK
So when you select a Fixture Type using a Combobox I would assume you then need a 2nd Combobox which would display only those products associated with the Type Selected?

 

[tmyers]

I think I have this one figured out now.
Thanks to everyone who helped on this!