[AccessD] Quotation Database structure question

Richard Lavsa rlavsa at tigg.com
Mon Jul 31 09:31:21 CDT 2006


Drew thanks for the screenshot.. I had the following structure in mind
when I saw yours structure it reassured me that this might be the way to
go.

I have something similar to what you have, or at least what I believe to
be a similar method to handle this issue.  Basically I have tables
(Components, ComponentTypes, ComponentTypeTemplate, ComponentValues)

ComponentType (Define a new type ex.(FAN, PUMP, Blower, etc)
--------------
CompTypeId
Type
TypeDesc

ComponentTypeTemplate (Build specs for each type)
---------------------
ComponentTypeTempId
CompTypeId
TypeSpecification

ComponentValues (store a value for each Type and Spec)
---------------
ComponentID
ComponentTypeTempID
CompTypeID (don't need this but did it for quicker queries)
SpecValue


Components  (Stores the basic info for the new component which includes,
Type, Price, QuoteDate came in from Vendor, and vendor)
----------
ComponentID
CompTypeId
ComponentDesc
VendorPartNo
UnitPrice
VendorID
DateOfPrice

Relationships..
Components 1 to Many ComponentValues (field: ComponentID)
ComponentType 1 to Many Components (field: CompTypeID)
ComponentType 1 to Many ComponentTypeTemplate (field: compTypeID)
ComponentType 1 to Many ComponentValues (field: CompTypeID)
ComponentTypeTemplate 1 to Many ComponentValues (field: compTypeTempID)

Ok, so now all the data entry forms are built, and this all appears to
be working correctly.

Now I'm down to building the Estimate itself.  So I have Estimate_Header
with your basic header information, and then the Estimate_Details.  The
Details will have Components, the quantity, Price,..... 

So my new delima is how do I display this data driven metadata type
system so that I can show the TypeSpec field from the
ComponentTypeTemplate as RowHeaders in a ComponentSelection form for the
Estimate Details section.  Feasable I know how to do this in a Crosstab
query, however I cannot display a raw query in a form (or can I and I
just don't know how).  I tried the PivotTable view in a form, but it
adds totals for every level you enter in the row Data portion of the
grid.. 

Any tips/tricks would be greatly appreciated on how to display this data
that is in a table, and show it as row header data.  Currently I have a
listbox, that works pretty good, however there is no sorting or column
resizing capability within it or else it would be perfect for this
situation as it dynamically rebuilds when a new Type is selected.  

Thanks in advance..

Rich


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
DWUTKA at marlow.com
Sent: Monday, July 24, 2006 12:54 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Quotation Database structure question

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



More information about the AccessD mailing list