Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to build an EER Model for a Autostore that has 5 locations and offers a range of auto products. They offer car repairs and roadworthy tests as a service also.

I need to be able to make fortnightly reports on unfinished service jobs, and fortnightly reports on the sales. They have a wide customer database filled with full addresses.

There is a constant inflow of new stock items and restocking of old ones. There should also be a way to know the cost of each item in stock and where its being held.

I swear I've researched it enough to be able to understand it by now but Im really struggling to map this out as I'm constantly running into a wall when dealing with the products that are being restocked, sold and stocked by particular stores in different locations.

I'm a total rookie with this kind of thing but if anyone can help me it would be amazing.

share|improve this question

1 Answer 1

This appears to be a fairly standard multi-site case. Model the data as if there is only one location. This will get the basic model down correctly. Then add a location column to the tables where location is important. Options you may want to use are:

  • Simple foreign key. (Transactions for incoming/outgoing stock.)
  • Multi occurrence foreign key. (Transfer records.)
  • Primary key column. (Inventory records.)
  • Splitting tables into core data and location specific data (Customers, product details.)

I have the AUTOSHOP "stocks" PRODUCT, AUTOSHOP "has" CUSTOMER, CUSTOMER "purchase" SERVICE, CUSTOMER "purchase" SALE, SALE "uses" PRODUCT, SUPPLIER "supplies" STOCK plus all the necessary attributes for each one. is that correct

I would remove AUTOSHOP entirely, you should attributes that are appropriate for LOCATION. Depending on how you are modelling PRODUCT, it may be fine. The AUTOSHOP stock PRODUCT relationship, likely should be an INVENTORY table. SALES (Invoices) usually have line items which may be either SERVICES or PRODUCT. I find it is better to model in the general and specialize as needed. The EER model is intended to make specialization simpler.

From a business perspective, I see three related subsystems: Sales, Inventory, and Purchasing. These three systems will all share Product, and Location data. In a single site system Location would not be required, or at least would not need to relate to the other tables.

share|improve this answer
    
In terms of setting out the EER-Diagram, I have the AUTOSHOP "stocks" PRODUCT, AUTOSHOP "has" CUSTOMER, CUSTOMER "purchase" SERVICE, CUSTOMER "purchase" SALE, SALE "uses" PRODUCT, SUPPLIER "supplies" STOCK plus all the necessary attributes for each one. is that correct? –  Jacob Rich Sep 13 '12 at 2:53
    
It is not valid to remove the "Autoshop" as it represents the point of sale. Also for @JacobRich, please include what you already have in the body of your question. The association "Autoshop" has "Customer" may not be valid because a customer can visit any branch of the company (unless branches don't share customer data or branches are geographically located in such a way that makes sharing customer data meaningless). Also, a supplier may supply 1 or more "Stock Items" and a "Stock Item" may be supplied by 0,1 or more suppliers., It depends on your business rules. –  Emmad Kareem Sep 14 '12 at 3:18
    
@EmmadKareem Location would be a better name for modelling the Point of Sale, than Autoshop which appears to the be the Application. –  BillThor Sep 15 '12 at 16:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.