Many many to many relationships I believe and how to break this down

[Macawac1]

Hi folks - back again!



Have now been reading, playing, thinking, rethinking etc.. for what seems like forever! My poor understanding just cannot figure it out. I may well be overthinking something that is quite simple but now cannot see the wood for the trees so to speak!

We manufacture control panels. At the moment these are bespoke to the customer. They can be a one off for a specific single order or multiple quantities over multiple orders. So far so good - this I can understand. Sometimes the components for the panel are specified by us - sometimes they are specified by the customer - by which we have to abide.

I am struggling with the relationships between the customer (or our) parts specification on a control panel's parts list and how to relate this to suppliers and manufacturers. This is because the customer (or us) may well specify the manufacturer for the part - for which we may have many suppliers and choice of supplier is up to us. Sometimes the customer (or us) will specify just the generic part - and then choice of manufacturer and also supplier is up to us. Where the choice of supplier, or supplier and manufacturer is up to us we make the decision on final choice at the point where we order in the components for the panel - not at point of devising original parts list. This combination of selections may well change for repeat orders going forwards based on facts such as manufacturer or supplier part availability, price, lead times etc.. Original parts list will occasionally change when there has been a drawing revision that may account for discontinued parts or an upgrade to the control panel design. (If I could figure out the parts list conundrum this can be managed with a parts list revision history table I believe.)

I can link generic parts to manufacturers and then from manufacturers to suppliers - but am struggling with a way to build a record of customer specifications that deals with either a specified manufacturer's part (from which we then choose supplier) or a generic part (from which we then choose manufacturer and then supplier). I am also struggling with a definition for a "generic part". At the moment I have a Parts table with PK = PartID (Autonumber), Subcategory, Description, which links to a Category/Subcategory table and onto a Category table. I can however see the danger with this approach as the parts table is reliant on "description" and so could easily contain parts that are the same but described differently. We need some way to record "generic parts" as sometimes we or customers design a panel where the parts list specifies just (for example) 24amp power supply - not manufacturer or supplier - which is defined at point of ordering in the components (sorry repeating now!).

Sometimes the customers supply their own part reference numbers that relate to a specific part. These can change occasionally depending upon who put the original parts list together (ie parts appear in several parts lists but with different customer part references referencing the same part - which may be a "generic part" or a part for which the manufacturer is also specified (never the supplier - that choice is always down to us)). This I believe I can tie up with a DrgPart/Part table - but only if I can figure out a robust way of defining a part and relating that to whether or not a manufacturer is also specified!

I have probably asked way too many questions in my ramblings above, but any pointers or advice or clarification on where my thinking is not quite right will be more than welcome!

Regards,
Karen.

[orange]

Do you have a list of business rules/requirements that you could share?
Have you tried to draw a data model (paper and pencil) of the "conundrum"? It would be a good starting point, even if at a high level. It would also be helpful when communicating with readers here, or colleagues at your work.

If you have a model of some sort, perhaps you could post it.

There are several free generic models at Barry Williams' site.

Where exactly are you with this project? Planning, design, development...?

Here is a link to info on Database Planning and Design
Good luck with your project.

[Macawac1]

Hi Orange,

Still very much at the planning stage.

My difficulty lies in trying to define processes that at this point do not exist. Everything at the moment is kept in the head of the Operations manager. Even he cannot give specific "rules" on how things tie together other than he just knows it (His "method" has existed for over 20 years - printing off the sales order and writing all over it with details on which supplier he has ordered the required parts from and the date he ordered them. As to which parts are needed for each order - that comes from him knowing the drawing requirements if there is one - along with all the different connotations and options that come with that). So I am maybe jumping very very far ahead with trying to build a database to do this - the processes really need to come first.

That said - thinking in terms of how to define things in database terms also helps with the processes - maybe back to front but I see a benefit to it. (I am gradually pulling things from the Operation Manager's head - but at the moment to a convoluted set of Excel spreadsheets.)

Due to a change in business ownership two years ago a lot of things are led electronically by Sage - Sales orders - Purchase orders - Products supplied - Components bought. (Sage was used before but only for accounting purposes). Unfortunately the information currently held on Sage is inconsistent and a lot just incorrect (more to do with parts, current pricing etc..). Not just that but an accounts software package is not a factory management solution - the two as far as I am concerned have totally different purposes.

The Operations manager is my husband - I only joined the company 11 months ago to try and take some "weight" off him and help him out a bit before he sunk - due to the demands of the new business owners and also to his own aspirations that have always existed. I was very happily employed by Leicestershire Fire Service for many years before this (Health and Safety - not process management unless it was HS lead and inspired!!)

So to summarise - very very much in the planning stage!!

Kind regards

Karen.

[orange]

There's a lot of database planning and design info in the link I provided.
The concepts and knowledge nuggets are often basic requirements to design.

Here is another link to "stump the model" which starts a collective focus on the issues .

I'm not an Excel person, but any record keeping is useful. But in the end, a model and vetting will lead to a blueprint for your database design.

Interviewing, taking notes, identifying processes and inputs/outputs and confirming same is a proven approach.

You have not talked about your database skills.

[keviny04]

One way to figure out a model is to analyze past data. Look over your Sage data, Excel data, and even his old paperworks. From them, you need to figure out the relationships among the data. Access is a relational database program, and knowing the relationships is the most important thing.