Curious Database building Problem (ER Diagram)

Question

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.

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.