CODEWITHSUNDEEP
sql-serverdatabase-designrelationships
robert
robert

Reputation: 11

Would you mind sorting through a few Beginner SQL invoice database creation questions?

I'm somewhat new to SQL.

I need to make an invoice that can be queried to create a document that looks something like this:

Screenshot

Here's the crow's foot diagram I made:

crow's foot diagram

I'm not sure if the customer shipping and billing address should be a separate entity. I'm also not so sure about the tblProductLine relationship to the tblItem and tblInvoice. Did I diagram it right?

Did I match the schema and setup the relationships correctly? Also, Could someone explain when foreign keys are necessary ? (since I wasn't sure where to put them) I'm assuming they go at the end of the 1 to many relationships to reference the previous table that called it, but I'm not sure... which tables need a foreign key?

Anyways... any help or comments would be great!

Upvotes: 0

Views: 1433

Answers (2)

Tim
Tim

Reputation: 5421

Typically ORDERS and INVOICES though very closely related are decoupled; you're conflating them into a single entity.

The CUSTOMER places an ORDER for one or more PRODUCTS. That generates the Order Header and Order Detail. The merchant issues an INVOICE referencing the Order. But in a very simple mom-and-pop operation you could dispense with ORDERS and let the INVOICE entity represent the order.

        ORDER|INVOICE DETAIL
        id  int PK
        headerid foreign key references INVOICEHEADERS or ORDERHEADERS *mutatis mutandis*
        productid foreign key references PRODUCTS(id)
        quantity
        extendedamount

Foreign keys are necessary because they prevent things like creating an order for a product that does not exist, or invoicing a customer that does not exist. They ensure that the database row contains no impossibilities.

Typically you'd have CUSTOMERADDRESSES as a separate table that refers back to CUSTOMERS. A customer can have one or more addresses.

Whenever an entity can "have one or more of X" that's a sign that you need a separate table to capture the Xes.

Upvotes: 1

Peter Lang
Peter Lang

Reputation: 55514

Some thoughts in random order:

  1. Your crow's foot is on the wrong site for some entities. An employee can have several invoices, but I assume that an invoice is "owned" by one employee.
  2. Why does your Employee have an InvoiceID? They can certainly have more than one invoice?
  3. Consider adding an Address-table and give your customer a ShippingAddress and a BillingAddress (optional), both referencing Address.
  4. What is the difference between Quantity and Amount in tblProductLine?
  5. Where do you get the price for Shipping and Handling from?
  6. You're storing the size in an extra field, but not the color. Color could be part of the ItemDescription, but then you can't really find out that the red and the blue Jag Something are the same product
    Depending on your requirements, it might be better to normalize that table and move size and color into a sub-table. If you need to rename a product, you only change one record instead of many.

Upvotes: 1

Related Questions