DWUTKA at marlow.com
DWUTKA at marlow.com
Mon Jul 24 11:53:49 CDT 2006
I created a product database for our company. Our website uses it. We have several 'types' of products, and I didn't want things hard coded, so I created a database where the people that control the data can create new products and put it in the system. Here is a screen shot of the relationships: http://www.marlow.com/productdbrelations.bmp there are a few tables that are off on the side, but those are strictly for the shopping cart itself. A little explanation: tblProducts is the main table, the ProductID is an Autonumber in that table. It has fields that are specific to every product, no matter what it is. The key field (in importance) is the GenericType field. I goofed when designing this, because I didn't use that term throughout the whole project, and didn't catch that I wasn't doing that until I was pretty deep into it. What does the GenericType field do? It tells the system what kind of product it is. GenericTypes are defined in tblSpecificationHeader (with a name, the GenericType field is a number field that matches the ListID (AutoNumber)). The specs for that product are then listed in tblSpecificationDetails. Each spec has the ListID (to relate it to a 'generic type'). Then have a name, units, (Our website has a glossary in it, so the Glossary ID matches the glossary terms to appropriate specs (for instance, some of our products are TECs (Thermo Electric Coolers, and DeltaT is a spec, and a glossary term, so when the website displays a product, if it has a matching glossary term for the spec, it shows up as a tool tip on the website. Then there are product types, and sub types. In our particular case, our subtypes are categories based on our customer base (DSP (Defense Space and Photonics), Commercial, Medical, etc.), and the types are more specific product types. For example, our Generic Types are 'standard coolers', 'accessories', and 'TTRS'. Our product types are identical for accessories and ttrs, but standard coolers have many product types (MI coolers, AT coolers, DT coolers, XLT coolers, etc.) I'll try to explain this in a little less technical terms. Each product has a 'generic type'. For instance, a centrifugal pump and a positive displacement pump are both pumps. So they would both have a GenericType 'Pump' (which would have it's own ID (ListID in my database)). The specs for a pump might be GPM, Type, PowerUsage, etc. Each spec for a pump would be listed in the SpecDetails table. Ooops, almost forgot. There are a few tables missing from the relationships. tblDateData, tblLongData, tblDoubleData, tblTextData and tblCurrencyData. They are identical tables, with SpecID (Long Integer), ProductID (Long Integer) and Data (data type matching the table, ie, Long Integer for tblLongData. Now, you might want to make a few modifications to my design. For example, my Product Specs are hard linked to a generic type. You may want to put this link in a many to many table, ie, a ListID and SpecID table, so that the same spec may relate to multiple generic types. (So if you have Manufacturer in many generic types, you can list all products, regardless of generic type, for a specific manufacturer. That may apply to all products though (so it may be put in tblProducts), but something like PowerUsage may only apply to certain generic types, so that might be a better example). Hope this helps. Drew -----Original Message----- From: Richard Lavsa [mailto:rlavsa at tigg.com] Sent: Monday, July 24, 2006 7:44 AM To: accessd at databaseadvisors.com Subject: [AccessD] Quotation Database structure question Hello all, I have a question on how to build a piece of my application. The piece I am having trouble trying to put together is as follows. Our engineers call out and get prices on material (aluminum, steel, Stainless), as well as components (Fans, Blowers, Pumps, Pipe, Angle Iron, etc.. ) So what they ask me to put together is a database that they can enter all this information into, and pull out information as needed. It sounded like an easy task at first. I started to lay it out and realized that even though these are all "components" to whatever it is they are building, they may have different characteristics to maintain in the database.. This is my biggest hang-up... how do I make a Component table be able to take into account all the various characteristics for such different pieces. For instance... FAN =3D (CFM, HP, RPM, Make, Type, Model, Cost, Vendor) Pumps =3D = (Style, Make, Model, GPM, Head, Type, Size, HP, Phase, Motor, Cost, Vendor) Starters =3D (HP, NEMA Rating, Voltage, Cost, Vendor) Then you have your Pipe and Pipe Fittings Pipe can be made from various materials, lengths, and thicknesses Pipe fittings can as well not to mention there are elbows, T's, Y's, etc etc... ... So in summary.. What I'm trying to build at this point is a way to record what the engineers received from various vendors and date it. Organize it in such a fashion they can pick and choose based on previous information gathered or based on updated data entered into the system and put together a quick quote for a customer. This will end up as a collaboration tool as well since some of the engineers may call the same vendor asking for the same if not similar items. I simply don't know what the best approach is in organizing all these Parts. They start off as a part, but they all have such different characteristics to record, I don't know where to begin. Any help would be greatly appreciated. TIA, Rich -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com