So I am new to database design and I am trying to make a model of my invoicing system. I am wondering if this is correct: table {customers} PK CustomerId table {Invoice} PK InvoiceId FK CustomerId table {IvoiceDetails} PK InvoiceDetails FK InvoiceId FK VehicleInfoId FK SignatureID table {Vehicle} PK VehicleInfoID yr mk model table {signature} PK SignatureID attachment basically, I am collecting table information in the invoicedetails table. Is this the correct way to do this?
Looks good. The only adjustment I'd make is to notice that InviiceDetail PK is never stored anywhere else, so it it unneeded. It is a Mapping table. Mapping tables usually don't have auto-increment PK. get rid of that column since you will never be able to JOIN on it Just make the index for it the combination of FKs that create a unique identifier. This is known as a composite index. [edit] The InvoiceDetail table should also have customerID as an FK. So the composite index would be InvoiceID_FK and CustomerID_FK, assuming that combo is unique, or maybe just InvoiceID_FK is enough. The other FKs are just for lookup
I don't guess it does. I was following the access 2013 bible example. The only thing is that i'd need a way to set my invoiceNumber and from what you told me, I probably shouldn't a pk in my lookup table. so my OrderDetails Table would look like this: table {orderdetails} PK InvoiceNumber FK CustomerId FK SignatureID FK VehicleInfo I really don't know how that would work
Actually it does hold the date the job was done. Plus I might need it to associate it with my invoice number. table {invoice} InvoiceId CustomerID DateofJob table {invoiceNumber} PK InvoiceNumberID FK InvoiceId table {orderdetails} FK InvoiceNumber FK CustomerId FK SignatureID FK VehicleInfo
Nah. You don't need a table just to store the Date. That is part of orderdetails. OK, so now that I have more info, ignore my other comments beyond just the theory of it. In practical terms, just put Date in the OrderDetails. So, OrderDetails is a mapping table to Signature and Vehicle Info, has a date, plus it can be joined on CustomerID. Those three tables above can be just one. tabel {OrderDetails} PK InvoiceNumber FK CustomerId FK SignatureID FK VehicleInfo DateOfJob What I'm trying to simplify is PKs are needed for Indexing the DB, so it sorts fast. But they are often overused, since composite Index provide the same speed. The way I think about it is if a PK is never stored in another table, then you can never reference it in code, except when humans enter it in a form like CustomerID and InvoiceNumber. So, In your case, I no longer believe you need composite indexes, but rather you should just get rid of all tables in between Customer and InvoiceDetail. just do Customer Table PK Customer ID customer data Invoice Table PK Invoice Number FK Customer ID FK Vehicle Data FK Signature File DateOfJob InvoiceDate ReceiptOfPayment etc about payment info So, just four tables. One for customer data, one for Job Data/Invoicing, and two for detail tables that might grow in width or are rarely used, Vehicle Data and Signature.
the only issue is your invoice details don't have a line number column which should be a part of your PK table {IvoiceDetails} PK InvoiceDetails ( InvoiceID, LineNumber) FK InvoiceId FK VehicleInfoId Signature should be in the Header, unless you do partial delivery.
Yes, I know. I am working on that part. It is a little more complicated by the fact that the services I sell varies in price depending on if the method of payment. I am thinking that I would have a payment method table: table {payment_methodID} Payment_method PK cash FK check FK credit card FK insurance FK table {insurance} insuranceID PK PricingID PK statefarm farmers etc Quanity table {cash} PricingID PK Quantity table {pricing} PricingID $50.00 $60.00 Etc
