Multiple Product Description and Quantities?

[tedzbug]

I am working on normalizing my DB and wondering if this is the proper
approach.

I have Quotes with multiple items on them and each item can have
multiple quantities with a price.

Do i need a separate table for the items and then a sub table with the
quantities?

http://i118.photobucket.com/albums/o117/tedntss/dialogue.jpg

An example of my current work in progress...............

 

[tedzbug]

btw, the tooldescription on that example is already refined to single
line entries

 

[John Vinson]

I am working on normalizing my DB and wondering if this is the proper
approach.

I have Quotes with multiple items on them and each item can have
multiple quantities with a price.

Do i need a separate table for the items and then a sub table with the
quantities?

http://i118.photobucket.com/albums/o117/tedntss/dialogue.jpg

An example of my current work in progress...............

Don't confuse data PRESENTATION with data STORAGE.

It looks from your graphic that you're making a couple of mistakes.
Each line item actually has four lines for different categories of
that tool. In a properly normalized database you'll have a Table for
each class of Entity; it sounds like you have a one-to-many
relationship between Tools and special cases of tools (Solid Carbide
vs. blank...? what's the blank mean?). The entity with a price is the
second table.

I'd see the following tables:

GenericTools
ToolNumber <primary key>
Description
<other fields about the tool as an object>

SpecificTools
ToolNumber <link to GenericTools>
ToolType <e.g. "Solid Carbide">
Price

Quotes
QuoteID <Primary Key>
QuoteDate
CustomerID
<other fields about the quote as a whole>

QuoteDetails
QuoteID
ToolNumber
ToolType
Quantity


John W. Vinson[MVP]

 

[tedzbug]

I will try to clarify, The line item 1 has a tool name and description
and quoted quantities of that single tool.

(1) (T43106312D, Solid Carbide, Coolant Fed, Coated) (Quantities and
Prices)


Ted

 

[John Vinson]

I will try to clarify, The line item 1 has a tool name and description
and quoted quantities of that single tool.

(1) (T43106312D, Solid Carbide, Coolant Fed, Coated) (Quantities and
Prices)

I'm sorry. That confuses things further, rather than clarifying them!

Please list your tables with the relevant fields, in the format

Tablename
Fieldname (Autonumber, Primary Key)
Fieldname (Text)
Fieldname (Currency)

Table2Name
Fieldname...


John W. Vinson[MVP]

 

[tedzbug]

Let me try again........ I think this is what i need in the two tables.

ITEMS
Line# (autonumber)
Quotenumber (number)
Item# (number)
tooldescription (T43106312D, Solid Carbide, Coolant Fed, Coated)


QUANTITY
ID (Autonumber, Primary Key)
Quotenumber (number)
Quantity (number)
Price (currency)

 

[John Vinson]

Let me try again........ I think this is what i need in the two tables.

ITEMS
Line# (autonumber)
Quotenumber (number)
Item# (number)
tooldescription (T43106312D, Solid Carbide, Coolant Fed, Coated)


QUANTITY
ID (Autonumber, Primary Key)
Quotenumber (number)
Quantity (number)
Price (currency)

Well, the Tool Description would seem to me to be better in a Tools
table - not in the Items table. If you include a T43106312D in a dozen
different quotes, surely you want to store its description ONCE, and
join it to your Items table by Item#, right?

And - your Quantity table doesn't indicate what it's a quantity OF.
Surely a Quotenumber doesn't have a quantity, or a price?

How about:

Items
ItemID <Primary Key> <don't use # in fieldnames, it's a date
delimiter>
ItemDescription
UnitPrice

Quotes
Quotenumber <Primary Key>
<information about the quote as an entity, e.g. quote date,
customer, ...>

QuoteDetails
Quotenumber <link to Quotes>
ItemID <link to Items, what you're selling>
Quantity <how many you're selling>
UnitPrice <filled in using VBA code from the Items UnitPrice,
assuming that the price may change over time or that you want to be
able to edit the price independently in each quote>

This would let you add the same item repeatedly to a quote if you wish
to do so.

John W. Vinson[MVP]
John W. Vinson[MVP]

 

[tedzbug]

The only issue i still see is that Quotes may always have new tool
numbers (not re-using old or requoting old tools) and also may always
have multiple quoted quantites of each tool on the quote. and the
prices are based on the quantities so that no tool will have a standard
price with it.

 

[John Vinson]

The only issue i still see is that Quotes may always have new tool
numbers (not re-using old or requoting old tools) and also may always
have multiple quoted quantites of each tool on the quote. and the
prices are based on the quantities so that no tool will have a standard
price with it.

Okay, you can still design the tables to accomodate your business
rules (which I did not know because you did not post them).

I'm now quite unsure what you are asking.

John W. Vinson[MVP]

 

[tedzbug]

Thank you for your assistance......... I will try to clarify and ask
the question again later.