many-to-many - revisited - crazy

[thadson]

Hi Everyone,

I'm trying to figure out a sane way to build a database that seems more and more complex as I'm looking at it.

I understand the basic principle of many to many relationship with the little middle table trick. But the way I need it isn't that simple

So here it is:

There are clients that can have many projects. Unfortunately, the projects can belong to many clients as well. So far it is straightforward: 1 tbl_client table with a lng_clientid and 1 tbl_project table with a lng_projectid and the middle table, called tbl_ClientProject including only the lng_clientid and the lng_projectid fields, linked properly...

But then, I have devices that belong to many projects and vica versa.
After that I have drawings that belong to many devices and vica versa.
Then I have drawings that belong to drawings and vica versa.
And last but not least I have part numbers that belong to many drawings and many drawings that have the same part.

Applying the same many to many principle, craps out the database.
IE: after making the many to many relationship between clients and projects and then try to make the "tbl_project - tbl_projectdraw - tbl_draw" relationship using the same lng_projectid and the lng_drawid fields, it does not show anything.

let me give you a concrete example:
Clients: CompanyA, CompanyB
Projects: modem, router,
Devices: modem and powersupply, router and powersupply, and stand-alones
drawings: modem box, router box, modem board, router board, modem powersupply same as router powersupply
drawings 2nd layer: modem box feet assembly (same as router box feet assembly), power cable assembly drawing, patch cable assembly drawing
parts: rubberfeet feet, feetscrew, pcb screw, capacitor, resistor, electrical wire, electrical connector, cat5 wire, cat5 connector and so on...

Both company A and Company b can order the modem or router project.
it is possible that both modem and router can have the same drawings like the box could be the same, but PCBoard will be a different drawing for both
On the main drawing there could be parts listed like screws or gromet that does not belong to any drawings but the main one but also drawings that are part of the main drawing.

I do not want to list drawings in 2 separate tables as main and secondary drawings as a secondary drawing could become a main drawing if a client orders the manufacturing of only -lets say - the powersupply.

Plus the 2nd layer drawings also could contain some more drawings beside parts and these 3rd layer drawings again more parts and drawings. These projects could get quiet complex.

The whole purpose of this mess is to figure out what parts are needed when a manufacturing order comes in, so we dont have to go through every drawing every time, as some of these projects have like 60-100 drawings and secondary drawings and hundreds of parts.

I hope this makes sense...

Could anyone point me in the right direction to set this up?

Thanks

 

[DCrake]

Simple Software Solutions

What you need is a big white board and a board marker. Then phyically draw the relationships on the board as you see the relationships. Use different coloured pens for primary, secondary and junction tables. If you can achieve that then step by step set the relationships up using the same metholdology. This is known as SSADM. Structured Systems and Design Methodology. Or Normalisation. Build up the structure in stages and test regulary.

CodeMaster:

 

[The_Doc_Man]

DCrake, you beat me to it. A white-board and a couple of boxes of dry-erase markers is a great investment that also can be used for other purposes when done designing. So it is justifiable as a multi-purpose purchase. Not to mention that they are cheap enough to not bust most budgets. Even stingy bosses will often go for this.

The only other advice I add to what you said is, go buy a case of sticky-note pads to represent records in the tables and trace what happens to each when you are doing the things you propose to do.

 

[neileg]

I'll state clearly what DCrake and The Dco Man have implied. Unless you can get the design down on the white board (or a piece of paper or the tablecloth in the restaurant) then you will never get it right in Access. Access will only do what you tell it to do.