SQL ERD

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
LVL 1
CipherISAsked:
Who is Participating?
 
Ashok KumarConnect With a Mentor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Scott PletcherSenior DBACommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
CipherISAuthor Commented:
@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
 
Scott PletcherSenior DBACommented:
OK, clear enough.
0
 
CipherISAuthor Commented:
Thanks for the help.  It helped me get a start on it.
0
All Courses

From novice to tech pro — start learning today.