Hi.  I am new to database design, and am looking for comments on the
following database design.  Is it overkill?  Any feedback would be greatly
appreciated.

Thanks,

John

Table: Brand    - Fields: BrandID, BrandName
Table: Style      - Fields: StyleID, StyleName
Table: Pricing  - Fields: BrandID, StyleID, Price, Child (Boolean), {*filter*}
(Boolean)
Table: Customer - Fields: CustomerID, CustomerName, etc.
Table: Orders:    - Fields: OrderID, CustomerID, BrandID, StyleID, Quantity,
TotalCost, OrderDate, Filled (Boolean)

The only thing I would do different is in Table Pricing - Child and {*filter*}
can be combined into one Character field with a 'C' or 'A' flag (I assume
that they are mutually exclusive, that is it's either a child or an {*filter*}).

Wells

Hi.  I have built databases for doctor's offices, antiques dealers,
and couples planning their wedding recpetion (500 guests and their
rsvp/gifts/etc.).    One thing I noticed about your schema is that you
have locked yourself into a customer purchase problem as far as
history goes.  The instance of a purchase from a customer should be
separate from the supplied information to that instance.  In this
case, your styles.  The way your schema is set up now, you aren't
actually selling a "thing" but a style/brand association.  With
referential integrity set with the schema you originally envisoned, if
you deleted a style or brand, you would delete the purchase.  Not
good.  You need something that will outlive modifications to styles
and brands.  This would be a product table that would include
style/brand.  A child's version of a style/brand would then be a
separate product from an {*filter*}'s style/brand and would have its own
pricing.  More an that later.

Also, in the course of business, the store owner might decide to no
longer sell an entire brand and all of its styles, but you don't want
to lose the history of the customers to whom you sold that brand to
previously.  Use your brand table and style table as lookup values in
a third table that is not transient (product table).  If the style
changes, if the brand changes, no problem  -- but you do not want to
cascade delete old purchases just because you delete a brand or style.

You also need to create an "active inventory" value.  This does not
indicate that it's in stock, but that it is something that you sell,
which is a slightly different idea. You might be totally out of men's
blue sweaters, but you sell them if you had them.  You can decide to
control this problem at the brand level or the style level, or both.
For instance, if I suddenly didn't want to sell Levi products, period,
regardless of the style, I would flag Levi as "inactive."  Then
wherever this information is used, I  test the brand active flag
before doing anything else.  If it's false, then i don't make it part
of the resulting dynaset that's available to sell from (this can be
done using a VB-defined function called in the row source query which
tests each value as the table is loaded/requeried).  I can do the same
at the style level.  I might have a child's style and an {*filter*}'s style
of the same sweater, but if I inactivate the style -- boom -- neither
the child's nor {*filter*}'s version of that style sweater will show up on
the register to sell from.

 Rule: Never delete your brands and styles.  They become part of the
history of your commerce, both as inventory orders and direct sales,
and need to be tracked for tax purposes, too (your vendor management
system is the flip side of your pricing/purchasing history).

The category lookup table is a good external index that can be used to
quickly answer such questions as "show me all Men's," for instance.

And along the idea for commerce, you have to consider history and the
changing nature of a product.  For instance, in the example of men's
blue sweaters, the store may have paid $25 per sweater in bulk
quantieis of 100 per a particular invoice shipment, but then $18 per
sweater in bulk quantities of 100 in the next.  That's called supply
and demand and the price of gas that week to truck stuff to your
warehouse.  To track profitability, we need to separate out the "idea"
of a men's blue sweater (this idea would appear in the customer's
catalog) and the actual inventory item of which you might have three
or four hundred men's blue sweaters in a warehouse somewhere, whose
cost to you varies from sweater to sweater.

If life is very simple and the cost to the store will never change,
then inventory management (and calculating the shelf tax for your
state) isn't nearly the can of worms that I'm making it out to be.
The main consideration is being able to prove to the IRS that at a
particular time and at a particular cost to the store, the store
purchased a particular item from a particular vendor.  An invoice from
the particular vendor is enough.   If you want, let me know and I'll
include a vendor management function with the scheme below.  For right
now, we'll just look at cost accounting and sales management and
forget the rest.

I'm also going to add a price modifier to the product table.  A price
modifier exists in a separate table and might be something like "10%
off until January 20th 1999"
Your first price modifier in your price modifier table would be a
permanent entry "0% off"

Why a price modifier table?  I can quickly discount or raise the base
price of a sale item by category, brand, or style just by punching it
up and changing the sale modifier for that item.  If you really want
to play with the big boys, add multiple price modifiers at each level.
Ever heard of a Parisian or JCPenny commercial where it says "10% off
all Sweaters and now through Saturday enjoy an additional 30% off!"

That is a no-brainer situation for the store.  They have multiple
opportunities to modify the price, both in the category "this is a
sweater" and the brand "Liz Clairborne" and the end date of the offer,
which is in the price modifier table.  The cashier just sees one price
(but should know of the discount and how long it runs).  You can even
automate the adjustment of prices, so when something comes up that has
a modifier other than your default modifier of 0% off when the date of
the modifier has expired, Access can just set that Item's discount to
default.  It takes a little VB programming but it's not impossible.
options are endless here.  It's best not to mix dollar discounts and
percentage discounts.  Stick to percentage discounts and offer dollar
discounts at the counter (again, the cashier is aware of them).  

I'm going to create the broadest, simplest kind of price modification
possible by placing it at the brand level.  Again, you could have
price modifications (even the same price modification) listed for the
brand, style, category, etc.).

tblPriceMod
     PriceModID (autonumber)
     PriceModExp (date/time) (expiration date of the modification)
     ProdModExpiresYN (boolean)
     PriceMod (percentage)    

tblBrand  
      BrandID (autonumber)  
      BrandName (text)

tblStyle    
      StyleID (autonumber)
      StyleName (text)
      BrandID (long integer foreign key to tblBrand)

tblCategory
       CategoryID (autonumber)
       CategoryName (text)  (i.e., "{*filter*}", "child", "Men's")

tblProduct (this isn't the product but the idea of a product)
     ProductID (autonumber)
     StyleID (long integer foreign key to tblStyle)
     CategoryID (long integer foreign key to tblCategory)
     Price  (currency)
     QtyInStock (long)

tblPaymentMethod
     PaymentMethodID
     PaymentName ("cash", "layaway", "complimentary", "visa", etc.)

tblSale
    SaleID (autonumber)
    DateOfOrder (date/time)
    dateOfsale (date/time)  -- have this default to now()
    ProductID (long integer foriegn key to tblProduct)
    SalePrice (currency)  -- this is the final calculated sale price
    MethodOfPaymentID (long integer foreign key to tblPaymentMethod)

Notice that I did not include a price mod field in the product.  Price
mod is displayed and calculated when the product is punched up at the
register to arrive at the final sale price to populate the SalePrice
field in tblSale.  Do not leave SalePrice as a calculate field -- dump
the actual value in there.  This is a warm fuzzy so you know you're
keeping an actual history of the transaction (should any calculated
values change in the future that then may incorrectly report this sale
price on yearly statements, etc.).

Have fun. I left out tons of fields and lots of tables,  but we can do
some customer and vendor management next!
~Mark

On Wed, 30 Dec 1998 17:19:02 -0500, "Wells"

And of course it helps to add the PriceModID to tblBrand, which I left
out.  Easy fix.   You can also place your "inactive" flag just about
anywhere and everywhere you want to test it.

Thanks to all.  I learned a lot.

John