Re: Database Design + Parts Explosion
Posted by: anodynepres
Date: May 14, 2007 01:22PM

I'm sure I don't understand the details about your application, so some of this might not make sense.

I would probably leave the category as part of the part table as either a foreign key to your reference table or as an enum datatype. I really like the enum datatypes in mySql. When you can use them, the provide a really elegant way to enforce a check constraint. And that's really what I think we're looking for in this table. My guess is that every part will belong to some category or be "uncategorized" (which is essentially another category). I think it would be appropriate to leave that in the table.

If you have other reasons for breaking it out, you can reassemble very easily using a simple join on id/part_id.

One question I have is that do your assemblies possibly contain more than one copy of the component part? If so, I might add a quantity field to part_has_assembly table (I think that is the link table from my original post).

To get a list of items that are assemblies we could do something like:

SELECT DISTINCT part.id
FROM part
JOIN part_has_assembly ON part.id = part_has_assembly.id

You could also create a view that lists all the parts of a given assembly including the root part (you might try using a union in there). You could then query the view with a part id and pull back the part and all of its associated sub-parts.

the only thing you want to watch out for when working with this type of data is that you have the ability to crash your db server with some bad data. It's possible to create infinite loops by creating a circular reference in your link table. Then when you go to explode a full tree, your query will never resolve and you'll get a timeout, if you're lucky, and a crash if your not.

Options: ReplyQuote


Subject
Written By
Posted
Re: Database Design + Parts Explosion
May 14, 2007 01:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.