[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL ERD

Posted on 2013-02-05
6
Medium Priority
?
419 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

834 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