March 13, 2009 at 10:01 am
I am developing a database that is going to be used to store quotes, quote details and similar information. I came up with a structure that my Boss has asked me to request feedback on.
First let me lay the groundwork. The code standard defines that GUIDs are to be used as primary keys with exceptions to be discussed and approved.
The design calls for the following table structure.
Quotes
- QuoteKey (guid, pk, guid supplied by business logic)
- SiteKey (guid, fk to sites)
- QuoteNumberKey(fk to QuoteNumbers)
- CustomerID ( outside database fk only enforced by business logic)
- QuoteDate (date)
- ValidUntil (date)
- QuotedBy (nvarchar(100))
- RowVer (rowversion)
QuoteNumbers
- QuoteNumberKey (int, identity, pk)
- used (bit, default 0, not null)
The quote details structure is not relevant to the question. The issue here is two fold. 1. The lack of the GUID primary key structure in QuoteNumbers. 2. the concept of having a table whose sole purpose is to provide a unique value to its parent table when the parent table already has a guid pk.
Defense
My defense of this structure is that I am using it to solve two business problems.
1. The Quote needs a unique number for each quote. I could use business logic to generate this but the logic is complex in that I would have to have a permanent temp table of requested but not used quote numbers when generating a new quote number for a user. In the business logic scenario, I would be checking the last quote number used, adding 1 and then checking the temp table and adding 1 until I had one that was unique and then adding that one to the temp table until the quote was submitted and I could then remove the number from the temp table. This is needless complex in my opinion and involves way to many round trips to the database for my taste.
2. The quote number needs to be generated at the time the quote screen is loaded. This is so that the quote number can be communicated immediately to clients and other required people prior to the quote being fully entered and saved. If I have the quote number is a breakout table, then I have that number reserved for that user. The only way they would lose this number would be if the software crashed without saving the quote.
The reason for the lack of a guid pk in the breakout table is primarily that I only want to store the int value of the quote number in that table. This makes accessing the value more simple and saves on storage space. instead of storing the guid (16 bytes) twice, once in Quotes and once in QuoteNumbers to access a 4 byte int, I replicate the 4 byte value across as a foreign key and add a field (bit field 1 byte long) that allows me to actually insert data since an identity does not participate in an insert with INSERT IDENTITY OFF being set.
Hence the math is like this
16 + 16 + 4 + 1 = 37 bytes
4 + 4 + 1 = 9 bytes
Based on my design and defense, my question is this. Is there a better way to design this so that my users can get their quote number immediately and have it unique across multiple users? Comments? Critiques? Ideas?
March 13, 2009 at 10:13 am
March 13, 2009 at 10:19 am
Because to get the IDENTITY value back the quote record must exist. This eliminates the possibility of giving them the value as soon as the screen loads. In the current structure I can add a record to the QuoteNumbers table, mark it as used, and display that as the quote number before they select a site, or a customer id (both of which are required to save a quote).
March 13, 2009 at 10:51 am
What you are suggesting will work. You should put a unique index on the Quotes.QuoteNumber column and lose the FK to your QuoteNumber table. They really isn't a relationship there (IMO).
Here is an alternative solution if you have a Numbers or Tally table in your DB. To find out if a number is 'reserved' or not, join the tables together instead of using a flag.
SET NOCOUNT ON
DECLARE @Quotes TABLE (
QuoteID uniqueidentifier DEFAULT(NEWID()) PRIMARY KEY,
QuoteValue varchar(10) NOT NULL,
QuoteNumber int NOT NULL
)
INSERT INTO @Quotes(QuoteValue, QuoteNumber)
SELECT 'test1', 1 UNION ALL
SELECT 'test2', 2 UNION ALL
SELECT 'test4', 4
DECLARE @QuotesInProgress TABLE (QuoteNumber int PRIMARY KEY)
SELECT * FROM @Quotes ORDER BY QuoteNumber
-- 'Reserve' Quote Number
INSERT INTO @QuotesInProgress
SELECT MIN(N.Num)
FROM dbo.Numbers N
LEFT JOIN @QuotesInProgress P
ON N.Num=P.QuoteNumber
LEFT JOIN @Quotes Q
ON Q.QuoteNumber=N.Num
WHERE P.QuoteNumber IS NULL AND Q.QuoteNumber IS NULL
--Show 'reserved' quotes
SELECT QuoteNumber as 'Reserved Quotes' FROM @QuotesInProgress
--Show Next available Quote Number
SELECT MIN(N.Num) as 'Next Available Quote'
FROM dbo.Numbers N
LEFT JOIN @QuotesInProgress P
ON N.Num=P.QuoteNumber
LEFT JOIN @Quotes Q
ON Q.QuoteNumber=N.Num
WHERE P.QuoteNumber IS NULL AND Q.QuoteNumber IS NULL
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply