Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2002-11-06 : 12:09:39
|
| Hi,I'm building an internet application with a sql server backend. People will fill out an application that we store in the database. I'm trying to determine the best primary key approach. Identity column:Would work, but there is a slight chance the db platform could change. It would be nice if the program itself had a primary key routine. What is the max number of an identity column?Datetime stamp & random number:I could build a datetime stamp plus small random number string. This way there will never be a situation where the keys could max out (identity). Chances of 2 apps at the exact same time with the exact same random number are very small.GUID:The development platform I'm using can create GUID vaules (.NET). From my understanding GUIDs are random numbers, so there could be a point in time where many of the GUIDs are taken (always a chance to get duplicate values)I'm tending to lead towards the datetime stamp scenario since it also provides some meaningful info (when application was created), but I was curious if others had input.ThanksNic |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-06 : 12:16:41
|
| Option number 4: use the entities natural key.Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 12:23:20
|
Identity column:Would work, but there is a slight chance the db platform could change. It would be nice if the program itself had a primary key routine. What is the max number of an identity column? - if you use an integer column you'll get at least 2 billion values, if you use a decimal value you'll never be able to fill it to capacity.Datetime stamp & random number:I could build a datetime stamp plus small random number string. This way there will never be a situation where the keys could max out (identity). Chances of 2 apps at the exact same time with the exact same random number are very small.GUID:The development platform I'm using can create GUID vaules (.NET). From my understanding GUIDs are random numbers, so there could be a point in time where many of the GUIDs are taken (always a chance to get duplicate values) - No. GUID stands for Guaranteed (or Globally) Unique Identifier. All GUID values are guaranteed to be unique for some ridiculous amount of time, over 100 years if I'm not mistaken, and there are more possible GUID values than there are atoms in the solar system. Seriously. SQL Server can generate the GUID's for you, it's better than having the application do it.I'm tending to lead towards the datetime stamp scenario since it also provides some meaningful info (when application was created), but I was curious if others had input. - Good, at least you're considering it, even if it doesn't work out as practical. Are there any other columns with real data that might be unique by design, or could be tweaked to be required as unique or semi-unique (combined with the date, for example?) This is Jay's option #4.  Can you provide more information on the what the tables are being used for, their current design (or design specs), and the relationships between them?Edited by - robvolk on 11/06/2002 12:24:20 |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2002-11-06 : 12:26:57
|
| I agree, using a natural key would be nice. But, the way the application needs to accept data, it is from "very general info" to "specific info". The application flow needs to be in this format so that it can provide the proper results. So, I won't really have a natural key until the very end of the application. I could carry all the info along until the end and then submit to the database but I'd prefer to save as I go.On the first few pages there won't be any natural key. Any preferances?Nic |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 12:38:40
|
| What kind of data is being captured, specifically? "General" to "very specific" is not what I mean. Is it name, address, phone number? The reason I ask is that it's possible someone has done something similar to your app before, and can provide first-hand recommendations based on what they used, but we need to know exactly what's being entered. |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2002-11-06 : 13:09:22
|
| It is for a car insurance application. The beginning of the application determines what state they live in, number of drivers etc. Depending on their responses to these "general" questions determines if the process can even continue. (its a waste of time to get personal info from the person only to find out that they live in an unsupported location). This will create a number of incomplete applications (i.e saving data for non-valid applications) but, after they submit enough data, I was thinking about swiching an isValid boolean column to true. If this value is false the data will be cleaned up when the internet session ends. (clean up)Saving the data along the way is nice since some of the intial data is needed later in the application. If it is submitted to the db then retrieval is easy (I don't want to have to store all the data external to the db before I determine enough specific data to create a natural key.)The more I think about it, the identity column is a nice solution (in general I usually use it). If by chance the app has an Oracle db I could create a insert trigger or something. Once again the chance of this is small.The specs are vague right now which is why I'm trying to determine a general approach.Nic |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 13:36:32
|
| So far everything you're suggesting sounds good. I would say that if someone is applying, you can put the general info into a separate "Applications" table, and only those people who qualify would get moved to the other tables. This would be where your isValid column would appear. If they get marked as valid, then you can fire off a procedure to populate the real tables and then remove the already validated rows. That way it doesn't matter if there's dupe info...they'd end up not qualifying each time anyway, and ultimately it would only hold people who don't qualify. If you're not already using a table like this you might want to consider it. Using an identity column in this table would be OK, since you'll basically be truncating it each day.You can use sequences in Oracle as an identity-type column. You'd have to modify some things if you move from SQL Server to Oracle anyway, and this one is pretty minor. |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2002-11-06 : 14:10:21
|
| Thanks that is a good suggestion. With the "pre-application" table idea, I have a question. The process involves supplying info for one or more cars and one or more drivers. So before completing the application, there would be a record in the "pre-application" table and one or more records in a "pre-applicationCar" and a "pre-applicationDriver" tables. Since a user could just quit the application at any point, do you think that I should have 3 of these "pre-application" tables and then after they submit ALL the data (and we know they haven't left yet), then I fire off a procedure that transfers all the validated records to the real tables (so only real records are saved, the "pre-application" records are just cleaned up when either they finish a valid application OR they end their internet session. So the isValid process completes at the very end. This solution has extra (pre and post application) tables but it will be cleaner in the long run.Sound good? (thanks for your help)NicEdited by - nic on 11/06/2002 14:11:18 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 14:48:07
|
| Yeah, having 3 tables (for apps, drivers and cars) are a good idea, you can join them together on the identity column from the pre-applications table. I'm a little fuzzy on the term "pre"-application though, but that's just me.Here's an approach I took on an app, I think you're talking about the same thing, so this is just to verify we're on the same page:I had to design a call center app with a data entry screen. Each call rep would call a customer and ask 5 screening questions. If the customer couldn't answer all 5, then the call could not continue, and there was no need to log it (in fact it was specified that way)If all 5 questions were answered, a follow-up set of 10 or so questions were asked. All of these questions, and the 5 screening questions, were recorded entirely on the web client. That way, if there was a power outage or a hang-up, NOTHING would be recorded in the database until everything was answered, and answered fully. Then the call rep would submit the web page and everything would be inserted at one time. This made it impossible to log incomplete calls, and therefore didn't require us to match up a customer to a call. Another way of saying it was that the call didn't exist until it was totally completed and logged in the database.I'm not saying that you should write logic to do everything on the web client, but you could at least collect all of the info you described and then insert it into the database in one fell swoop...it sounds like that's what you're doing or want to do. Once that's done, then you can have the server run the validation process on the data and then mark the application as approved/valid or not. That's why I'm fuzzy on "pre" application vs. application, they seem to be the same thing here, or overlap almost entirely. Once the application is approved then I imagine a policy would be issued, or some other process, that pretty much sets the customer in stone; they're not a "maybe" anymore. Then the car and driver info can be moved to the appropriate post-application tables.The only problem I see is that someone could submit info that immediately disqualifies them, but you can get around that with a "status" column: S for "submitted" (default), A for "approved", D or R for "disapproved/rejected". That might be a little better than a yes/no isValid column. You could even add a status value for Incomplete or any others you need. I've done this before too, where it wasn't always a yes/no question.HTH |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2002-11-06 : 15:18:30
|
| Thanks once again.What I meant by the "pre" and "post" was the "pre" was for an incomplete application. Using your call center app, instead of recording all the questions on the web client, I would insert data into the "pre" (pending/not complete) tables as the user filled out the various pages of the form. Then once all the data was collected (i.e. your call center collected all 15 questions) then I would transfer the completed application into the "post" tables (maybe once they filled everything out they could hit a "get quote" button which would transer the data.At that point they would be presented a quote even if that just meant that due to their 50 accidents, we can not provide them with a quote. (the status is "failed" or something)So the "pre" tables are pretty much a just storage bin until the form is completely filled out.So in the end, your call center saved incomplete applications in the web client, my app would save incomplete application in the "pre" tables <-- (this way the user COULD come back to an incomplete quote.)I hope this makes sense and isn't too off base?Nic |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 15:45:33
|
No, you're not off base. As long as you understand it, that's what matters most. Usually there's more than one "right" way to do something anyhow. If you need to have the ability for someone to come back and complete an application, then what you have will work fine.My situation was different in that it was all or nothing. If it was the DMV for example, in your app, they would hand back your incomplete form and make you complete it; in mine, they'd tear it up and make you fill out a new one.  That's why I didn't need to store intermediate results, but obviously that's not gonna work for you, so the "pre" tables are the way to go. |
|
|
|
|
|
|
|
|
best PK solution