Jewellery Business Design

[plastikman808]

Hi everyone,

I am new to DB design, but wanted to help a friend who is setting up his own business selling Jewellery. After speaking with him, the things that are important to him are



1.who are the best customers and in which brands
2. who are the top selling employees
3. how much stock has he got
4. How much profit he makes each month

I have had a stab at creating a DB which i think could be a good start, but i like some other opinions please





many thanks for any criticism good or bad

[Dal Jeanis]

Overall, pretty nice design. Here's some things to think about.

Profit is squishy, unless you are intending to build a full financial package. You'd have to deal with the fact that the Cost for a product will vary over time, and so you'd be doing LIFO or FIFO calculations or whatever. I would suggest making very clear that the profit numbers provided by this database are estimates, not accounting numbers. If accounting accuracy is desired, then design the database so that it can feed Quickbooks or Peachtree or whatever the latest flavor of accounting package is, and change CostPrice to CostPriceEstimate.

One alternative (I've forgotten what it's called) is a blended cost - If you buy 10 items at $45 and later buy 5 more at $60, then the total cost for 15 was $750, and each one, when you sell it, is costed at $50. There's also replacement cost, and various other methods.

In any case, I'd suggest moving CostPrice to a table of its own, and have starting and ending dates on it. Define it carefully - this is the CostPrice to be used for estimating profits on sales between date1 and date2. Similarly, I'd add a table for SaleListPrice with dates on it.

It's not considered "normalized" to have the sale price stored on each item on the ticket. However, as I understand it, in the jewelry business the actual item price of a sale can vary widely, even within the same day, based on store discounts, customer discounts, volume purchase/package deals, coupons, groupons, and so on. So, I'd say that's a good call. Keep clear track of what the store DID charge, net-net-net. (By the way, you need to account for tax on the sale somewhere, probably the sale record.)

I don't see a need for an outofstock flag, if there's a stock level of 0, we're out. However, you may want a "don't reorder" flag and/or a "discontinued" flag. You may want a "reorder level" field (number to keep in stock), and setting that to 0 means don't reorder.

I'd tend to add an address table, and use the same one for customers, employees and suppliers as well. (By the way, your employee may buy something - does he end up in two tables, or is it the same table, or what?) Have a link record that says for each what kind of address it is (mailing, delivery, etc)

You've got a good core there. After you've given it another once over, you might want to have your friend walk you through how they'd like to use the system, and see if the structure supports the business need.

Ooops. Is this an internet business, rather than a storefront? If so, then sale Zip Code and delivery Zip Code is going to be a big item. Determining the appropriate taxing authority and rate can be brutal.

[plastikman808]

Hi Dal, thanks so much for your feedback. My friend will just be a storefront. I definately had not thought about an employee buying something so i guess i should add a lookup field in the customers table from employees. I will add a table for costing on its own...been doing alot of reading especially Access 2010 the missing manual which seems quite helpful...once again many thanks!!

[Dal Jeanis]

While you're mulling it over, pop over to Roger's Access Library. http://www.rogersaccesslibrary.com/forum/forum46.html

MVP Roger Carlson has some great tutorials there on database design and application design.