Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
digitalslavery
Starting Member
6 Posts |
 Posted - 2008-03-14 : 20:34:10
|
| I am trying to figure out the best way to build a table that will hold information about our inventory of parts. We have several issues that I have been struggeling with trying to make this work. First capturing the basic part information is a no brainer, where I am having some questions is with how to assign categories, subcategories for each of these parts. The challenge comes because each part can belong to multiple categories and subcategories. For example we have 5 main items that would have categories, subcategories, parts. For example:Item: TrailerModel: model1, model2, model3Category: ChassisSubcategory: Upper frame, lower frame, electricalPart: could be any thing that falls under one of the subcategoriesItem: TrailerModel: model1, model2, model3Category: Gen setSubcategory: engine, power, electricalPart: could be any thing that falls under one of the subcategoriesrepeat...My main items are trailer, shelter, power, heat, lights. Under the trailer and shelter items parts can belong all models or just a couple or just one model. My thought was to create a table for each item, ie: TrailerModels, ShelterModels which would have 100 plus columns, 1 column for each model, using a bit datatype to show which parts could belong to which model. This however seems to be quite a bit of work, because in order to use the models to thin down lists when searching for an item it would have query this table as well as the category and subcategory tables.Anyone have some feedback about how I should approach this? I don't know if I was able to communicate the specifics of this very well but I can clear up details if anyone wants to throw some feedback my way.Thanks!yAy |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
|
|
digitalslavery
Starting Member
6 Posts |
Posted - 2008-03-16 : 07:47:48
|
| Thanks, I already read that before making this post. Looks like I will have to build out a table for each item, trailerModels with a column for each model, then when the user wants to add a new part the row will have to have a 0 or 1 in the column of each different model. The real drawback is having to add new columns as more models are added, plus if a part that already exists needs to be updated the user will have to go into each part one by one and update that part to include new models. I still do not think this is the best way to do it for some weird unexplainable reason. ;)yAy |
 |
|
|
evjo
Starting Member
20 Posts |
Posted - 2008-03-21 : 19:36:44
|
| hmmm it seems to me that if you have to keep updating your schema when new model types are added, then it is not a good relational design.I am a little confused by the description of what a part the difference between a part and a item is, are these 2 different things or the same thing?There are a few things that aren't clear to me like It seems to me that the sub categories all belong to the same category?So the sub category of Upper frame, lower frame, electrical always have the same category of Chassis?so that out with the 3 (or 4) basic tables of item / sub category / category and model. each of these should have an id column that is simple a number.what you have is a many to many relationship between each item and each category / sub category and model, so this means for each of these relationships you need to introduce a new table that contains 2 columns. 1 for the id of the item and one for id of the model / category / sub category. that way you can map each item to how many models etc you have without having to change the schema.eg.Tables: Item-----------<ItemModel>-----------Model Columns: ItemID ItemID, ModelID ModelID |
|
|
|
|
|
|
|
|
Building a parts table with multiple catagories