Now you can calculate each LineTotal as Quantity * UnitPrice, which you then sum to calculate your quote total ( QuoteAmt). NOT NULL, // which Part this line describes NOT NULL, // which Quote this line/detail belongs to Here is a better design for QuoteDetail: CREATE TABLE. Likewise, Quotes should not store its (quote) total, because that value depends on the data in QuoteDetail. A customer's name should be provided by the Customers table it should not be stored in your Quotes table. Since QuoteDetail is related to Quotes, which references a CustomerID, you don't need to store CustomerID in QuoteDetail again.Īny data that is "referenced", should always come from the referenced table.Quotes should not have a QuoteAmt column: this should be calculated from your QuoteDetail table.Having 20 "PartNumber" columns per record limits each quote to 20 items, complicates quote calculations, is error-prone and duplicates data. Refer to this good example on the third-normal form (3NF). Your referential integrity will take care of itself if you design your database correctly. INSERT INTO dbo.Quote(CustomerID, (list of other. INSERT INTO dbo.Customers(list of fields) (define list of parameters you want to pass in) In your stored proc, you can do something like: CREATE PROCEDURE dbo.InsertQuoteDetail UPDATE: if you want to insert values into QuoteDetail, you need to first have an entry in Quote and thus an entry in Customers. That's the much cleaner approach and design - this will allow you to store just two or three parts, if needed - but also 21, 25 if you ever need to - without changing the database! QuoteDetailID INT FK -> to QuoteDetail tableĪs its fields. QuoteDetailParts, which references the QuoteDetail detail and has QuoteDetailPartID INT PK Side note #2: I would strongly recommend you get rid of those twenty pairs of PartNumberX / QtyX in your QuoteDetail - put those into a separate table, e.g. 12345, right? So just reference Quote from QuoteDetails, and that's plenty good enough! Otherwise, you're just overcomplicating your model without gaining anything, really. 12345, then all the QuoteDetails that belong to that quote will also be relevant for customre no. I assume if a Quote is assigned to customer no. So what do you want to do when a QuoteDetail is inserted? How should the two other tables be "affected" ?Ī side note: since your QuoteDetail table references the Quote table, which in turn references the Customers table, there's really no point in having the fkCustomerId in the QuoteDetails. Likewise, you cannot insert a QuoteDetail which has a value of fkQuoteID that is not found in the Quote table. Referential integrity by using foreign key means you cannot insert a Quote with a value of fkCustomerID that cannot be found in the Customers table. What do you mean by ".quote and customer table are also affected". Why aren't my parameters available, despite declaring them at the start of my sproc? InsertCommand="AddQuote" InsertCommandType="StoredProcedure"> Here's the SQLDataSource defined on my ASPX page: " Also, when I try calling this sproc from my ASPX page, I receive : Procedure AddQuote has no parameters and arguments were supplied Values SSMS, the parameters are not visible in the treeview of this sproc. (QuoteDetailPartID, QuoteDetailID, PartNumber, Quantity) (CompanyName, Address, City, State, ZipCode, OfficePhone, OfficeFAX, Email, Primar圜ontactName) Update: Made table changes, here's the sproc now: USE Your advice/guidance on how to set these up so that customer ID in Customers is the same as in Quotes (referential integrity) and that CustomerID is inserted on Quotes and Customers when an insert is made to QuoteDetial would be much appreciated. IDENTITY(1,1) NOT NULL,įOREIGN KEY() REFERENCES. Here's the scripts for my tables as they stand now (please don't laugh):Ĭustomers: CREATE TABLE. I have tried my best to set up primary/foreign keys on my tables but need some help. I want/need to enforce referential integrity such that when an insert is made on QuoteDetail, the quote and customer tables are also affected. I have a SQL Server 2005 database comprised of Customers, Quote, QuoteDetail tables.
0 Comments
Leave a Reply. |