Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL ERD

Posted on 2013-02-05
6
Medium Priority
?
422 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 2000 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 70

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 70

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

589 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