Solved

SQL ERD

Posted on 2013-02-05
6
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

707 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