Solved

SQL ERD

Posted on 2013-02-05
6
408 Views
Last Modified: 2013-02-11
I need to build an inventory system.  I've attached an excel spreadsheet that i was provided but changed the data.  I need to normalize this for a database.

Thanks for the help.
Inventory-Parts.xlsx
0
Comment
Question by:CipherIS
6 Comments
 
LVL 6

Accepted Solution

by:
Ashok Kumar earned 500 total points
ID: 38858108
As far as i understand from the excel sheet,

you need to have atleast these tables.

1. Product library ( contains productid, productcode, description, price, supplierid, quanityonhand ) etc. will product details with category

2. Supplier table ( supplierid, supplier name, address1, city, state, pin, phone ..etc) will hold supplier details with his details.

3. Supplier-PO ( POid, ponumber, supplierid, productid, quantity, dateordered, ordered
by, datetimestamp, whenrequired, delivereddate, invoiceid)
3.a. Ordereditems ( idproductsordered, idproduct, quantityordered, price, datetimestatmp)

This will hold Purchaseorder to supplier and items contained, with quantity ordered with price that was existing at the time of ordering. Invoice id will get populated when invoice is generated.

4. Paymenthistory ( paymentid, poid, amountpaid, invoiceid, datepaid, etc)

will hold payments made to corresponding invoice in their currency.

5. Invoiceregister ( invoiceid, invoicetotal, date, idsupplier, generatedby )

will hold header details of invoice, with from & to address, totals, payment terms etc.
the items contained will be related to productsordered stratight.

might need surcharge & administrative charges.. in handling invoice.. look out for additional fields that might be required.

as soon as ordered quantity arrives from supplier, quantityonhand will increment per item as per quantity received.

all Date fields needs to be full ( dd/mm/YYYY)  your your date fields had only two entity...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38860070
What is your experience with Access database design...?

<I need to build an inventory system.>
This is akin to saying that you need to build a house or car from scratch...
;-)

Inventory systems are deceivingly complex.

Even inventory systems that seem to be "Simple", ...can end up being quite complex.

Just look at the suggestions made by SOTGarmentsIndiaPrivateLimited...
Even those suggestions cannot be taken as Gospel, and may need to be modified for your exact needs.  
So we can only provide "suggestions', not really provide a solid "design".

For example, see the "Inventory" sample Access database Template here.
http://office.microsoft.com/en-us/templates/inventory-management-database-TC001018458.aspx
(Why reinvent the wheel)

Examine all the complex table relationships, and Form, Report and query designs.
Finally look at all the VBA required for this *Simple*" database template...

JeffCoachman
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38860365
What SOTG posted isn't within light years of being normalized.  And it's extremely weak and inflexible.

CipherIS, you need to go thru the normal design process, NOT try to short-cut it.

First, do the conceptual design.

Then do the logical design:
describe the data elements you need;
determine the entities (important business object you need to keep data about);
assign data elements to entities, document any qs;
etc.

Finally, convert that logical design to a physical design, then denormalize only as necessary to implement the system.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:CipherIS
ID: 38864617
@SOTG - thanks for the info

@boag2000 - I am well aware of it as I am developing multiple systems and happen to be working on another inventory system which is nothing like the spreadsheet that I provided.  The access database template will not work because the customer has specific requirements which I have not disclosed.

@Scott - I am well aware of the process, however, I posted here for a reason.  I have architected some databases but I am not a DBA.  I didn't think that there was too much info in the spreadsheet which is why I posted here and thought I could get some help from a person who was a DBA.  I would have preferred that you provided ACTUAL help or not comment at all.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38864750
OK, clear enough.
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 38876541
Thanks for the help.  It helped me get a start on it.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question