Database Answers City Temple.

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
Cosmetics Home Sales

The Requirements have been drafted and are listed below.
The Area being Modelled is :-
Cosmetics Home Sales.

I would like to take you up on your offer to possibly help. 
I want to create an Access database for my wife's business. 
She sells Mary Kay cosmetics from home. 
Essentially, I want to create a database that will keep track of her inventory, customers, sales and expenses. 

This would also be used for tax calculations (COGS and expenses). 

Tables: 
Customer - This would have high level info on cust
Customer Profile - this would track profiles and make up, skin care, tastes etc. 
Orders - This would keep track of what the customers purchased
Inventory 
Inventory cost 
Sales - This is what the products sold for. Not sure if I need this separated. 
Expenses - keep track of mileage, items purchased for business, etc.
 
Questions: 
1) I am not sure how to handle inventory. 
   For tax purposes, we have been tracking beginning inventory at a point in time (beg of year) and ending inventory at end of year. 
   As such, in the db, I believe I will need to keep track of each item with a dates (date in and date out) rather than a total count 
   by item that gets updated when inventory is added or taken away. 

For example: 
 
Current Inventory count: 
 
ID, Type, qty
001, xyz lipstick shade, 20
002, abc lipstick shade, 5
 
Upon sale this table would be updated with the qty changed to: 
 
ID, Type, qty
001, xyz lipstick, 19
002, abc lipstick shade, 5
 
I think I know the answer will be that I will need to keep track of 
each item (each tube of lipstick) rather than a 
count of all the tubes of that shade, but I want to confirm. 
 
2) I was thinking I should break out the inventory and the cost. 

This is because in many cases, the product type (eg Lipsticks) all 
have the same price regardless of the shade. 

By breaking this out if the price of lipsticks changes I can update 
it in one place rather than all records. 

Am I thinking correctly on this? 
 
3) What is the best practice to handle sales. 
    Should it be tracked in the order table or broken out on it's own. Am I creating a redundancy by adding this table. 
    My thinking is that this table would need to show the selling price so I could calc gross profit (selling price - inventory cost). 
 
4) Expenses I believe will be a separate table (or tables) not linked as it is not connected to inventory. On this note, however, would I 
   need to link it to inventory to keep track of office supplies and items used in the business to sell the product (eg samples, etc). 

Brian McTeague"  

A. DESIGN NOTES :
A.1 The Detail reflects the Requirements.

B. The Things of Interest, ('THINGS'), include :-
B.1 Customers.
B.2 Inventory at different Locations.
B.4 Employees, which are Drivers in this Model.
B.5 Orders - which can be either Regular Orders and Actual Orders.
B.6 Products.
B.7 Trucks

C. These THINGS are Related as follows :-
C.1 A DELIVERY_ROUTES visits a number of DELIVERY_ROUTE_LOCATIONS.
C.2 A DELIVERY contains a number of ORDERS.
C.3 An ORDER contains a number of PRODUCTS.
C.4 A REGULAR_ORDER allows the Customer, (Distributor), to say "Send me my Regular Order".
C.5 An ACTUAL_ORDER allows for changes to the REGULAR_ORDER.
C.4 A TRUCK can be associated with zero, one or many ORDER_DELIVERIES.

D. Other Characteristics of these THINGS include :-
D.1 A CUSTOMER can have one or zero PHONE_NUMBER.

E. Sample Data includes :-
E.1 To be determined

F. Typical Enquiries include :-
F.1 To be Determined
 
A GLOSSARY OF TERMS WOULD GO HERE AS A TABLE ...
Barry Williams
24th. February 2003
Principal Consultant
Database Answers
Home Ask a Question Careers Contact us Data Models First Timers Search Site Map