hi, I am trying to design a database for my company. I asked in another forum and they said I was looking for a "parts explosion model", and I was wondering if anyone could help me. My company is in the trade of building printers.
the full assembly would be the printer, then it breaks down into sub-assembies (electronics, ink, roller) which are broken down into components, where some of these components are assemblies themselves. this goes on until things are broken down to a part that is not an assembly.

my problem is that there are different parts that have multiple "parents" so it has given me trouble in making the database, because I have not been able to reference some of the parts that have multiple parents.

thanks in advance

http://www.webmaster-talk.com/the-da...explosion.html

http://www.webmaster-talk.com/the-da...explosion.html

that doesnt explain how to accomplish it ?? i can see that he says he needs to have that 1 field that has the different parents, however HOW do you reference the parent field by looking at each individual parent seperately instead of viewing the whole thing as 1 field ?

by using a series of tables and mapping parent IDs to child IDs in a many to many relationship.
And then using JOIN queries pull back the children of a parent ID.

One schema could have all the component items in a monolithic table and then use a table that mapped component ID to component ID with columns of parentID and childID.

another schema could have tables of components that can only be parents with a table of child components

You also will have to consider the child components that can also have children and so on.

There are a few ways of planning it.
one would be to start with the individual components and design the DB schemas backwards.
which should lead to a more efficient structure
downside would be more complex queries.

at the opposite end you could start off with the whole and break it into assemblies, then sub assemblies and so on.
This would very likely lead to less efficient tables with more redundant data. The queries would be less complex but there would be more round trips to the DB.

All in all not a trivial task.

Hopefully you are not trying to do this in Access

at the opposite end you could start off with the whole and break it into assemblies, then sub assemblies and so on.
This would very likely lead to less efficient tables with more redundant data. The queries would be less complex but there would be more round trips to the DB.

we actually have a table for every "parent" however I know that this is really inefficient, for cost, maintainability, transfer.

my thought originally was that I would create one monolithic table and have the serial, description fields, then I was going to add parentId, has_child where the parentId is the serial, and has_child is either 1 or 0. What I have ran into doing this method as I described before is that some of the parts actually have multiple parents. Creating the query for something that has only a single parent is easy

SELECT 'serial, description' FROM 'Parts' WHERE 'parentId' LIKE 'serial';

I guess what I am trying to ask is there a way to "explode" data inside a database by some delimiter? then I could just run a search through the resulting array?
I dunno maybe I am totally off? Any thoughts?



// EDIT
would it be possible to maybe put in a wildcard for the serial ? %serial% ? would that be able to match the components with multiple parents

Hopefully you are not trying to do this in Access

gah! definitely not. All the data is in a program called SAP, in turn which I explode to an excel spreadsheet. the second half of my dilemma is after I get this all sorted and restructure the database, how can I make it so that I am not needed :lol: yah I said it :haha: The Goal of this whole project was to clean up all of the junk they have right now and transform it in a way that its easy for them to update, as there are anywhere between 5-15 changes a week, which means I am very busy at the moment, but I want to make it so whenever something is updated they just need to upload the BOM and it will auto configure itself HAHAHA and you thought the database structure was interesting

? anyone have any thoughts?