Table Design / Relationship Help (1 Viewer)

[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?

 

[Pat Hartman]

plog, I have several situations where I have what look like loops but aren't. One is in an application similar to what tmyers is trying to model. One "loop" involves Company Contacts and Jobs with a m-m as tblJobcontacts. A company has many contacts. A job belongs to one company a Job has many contacts but all must come from the company the job relates to. So you have:
tblCompany -- tblJob
tblCompany -- tblContacts -- tblJobContacts -- tblJob

So there are two paths from tblCompany to tblJob

The CompanyID in tblJobContacts is used to control the combo used to build tblJobContacts to make a list of the company's contacts for this particular job. Then in the rest of the app, wherever a contact is required, it comes from tblJobContacts because you only want contacts for the job you're working on. So in tblJobContacts, Joe's role might be supervisor for Job1 but siteManager for Job2.

 

[Pat Hartman]

tmyers,
I think you are trying to keep counts in tables when you should be just running totals queries to count stuff. So ContractorCount and ProductCount should be deleted. The path to make these counts is also suspect and so you might need to change how the tables are related.

I would think that product should be related to type and sheets should be related to product. I'm not sure what SheetCount is.

I modified the schema I'm using to remove all the lookup tables and some other stuff that is project specific so tmyers can see the guts of the relationships. I'll explain the less than obvious tables.

Drawings - these are the schematics that construction projects use. Some types show various views of a part with their dimensions. Higher level drawings show connections between parts so we can see that the knee bone is connected to the thigh bone. The highest levels might show building elevations.
Jobs - the name for the whole project.
Revisions - during the design and even build process, the specs for a part might change and so a replacement drawing is issued. These are called revisions. As you can see from the diagram it is revisions that are connected to the rest of the application, NOT the original drawing record. Every drawing created also generates a Rev 0 record.
Transmittals - these are documents that are the summary of the contents of a package. Drawings are sent to various people for review and approval or possibly notification. Some transmittals require an approval to be returned.
TransmittalDetail - each rev included in the Transmittal
Sequences - These refer to floors or perhaps sections that repeat. So some part might be in sequence 1, 3, 5 but a mirror image version might be in sequence 2, 4, 6. Or floors 1- 10 might have one layout and 11-20 might have a different layout and contain different components. So a bridge for example, might have left and right versions of the parts that make up the guardrails.

 

[Pat Hartman]

Just FYI, object names should never contain embedded spaces or special characters. You'll thank me later if you change the names now.

 

[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.

 

[Pat Hartman]

I don't know what "counts" are. If a count is the number of identical pieces that the sheet calls for, then the count goes in the sheet. If the count for the vendor means that he is making 3 of the 5 pieces, then it goes in a separate transaction table as you have it but it should be related to sheet and vendor..

I think the issue with multiple paths that plog referred to is related to pathological connections. i.e. connections with dependencies that cannot be fulfilled logically.

 

[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.

 

[Pat Hartman]

I would name the table SheetFixtureType because this is actually a junction table between Sheets and the FixtureType table. The FixtureType table defines all fixtures and SheetFixtureType relates them to a specific sheet. The count belongs in the SheetFixtureType table which is a child of the sheet table. You should have a "Job" table also or whatever you call the whole project. Then you would use a query to summarize the FixtureTypes for the job. There also may be a relationship between FixtureType and Vendor if specific fixtures always come from specific vendors. Then you can easily create a query that counts the Fixtures for a Job for each vendor.

 

[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.

 

[Pat Hartman]

TypeID does not belong in tblDrawings. There needs to be a table between drawings and FixtureType. That is a junction table and it contains
FixtureTypeID (autonumber PK)
DrawingID (FK to tblDrawings)
ProductID (FK to tblProduct)
Quantity

I just noticed a Product table. I don't understand what the difference is between Product and Type. Which is actually specified on the drawing? If FixtureType is a grouping, then the relation ship should be
tblDrawings --> tblDrawingProducts --> tblProducts --> tblFixtureType

Whenever you have a many-many relationship, you need a junction table. The junction table contains two 1-m relationships. So tblDrawingProducts contains DrawingProductID (autonumber PK)
DrawingID (FK to tblDrawings)
ProductID (FK to tblProduct)

Please clarify Type and Product so I can fix the suggestion above.

 

[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?

 

[Pat Hartman]

Since the actual part number is on the sheet, the sheet should link to the parts table and the part table should have the type. That will allow you to count by type or by part.

If you want to filter your part combo by type to reduce the number of options, that's fine. You might also want to filter by manufacturer to reduce the list even more.

 

[tmyers]

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