Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Response Guidelines
Message
From
05/01/2001 02:52:39
Walter Meester
HoogkarspelNetherlands
 
General information
Forum:
Visual FoxPro
Category:
Other
Miscellaneous
Thread ID:
00457550
Message ID:
00459684
Views:
22
Chris,

Sorry for the late respond, I've must have missed it.

>>Is there any disadvantage to store the invoiceno in a field called inv_pk ? I see your fond of using the word consistency, but IMO its not that simple. Applying consistency where it limits your options might catch you in the end. In general consistency is good, but in the case of this discussion, we should notice that both surrogate keys and intelligent keys do have its advantages and disadvantages (see http://www.bcarter.com/intsurr1.htm). Applying consistency here would automaticly mean that you're not be able to pick best from both worlds. This is specificly important when you want to take every advantage there is.

>In your system, is Inv_PK an 4-byte integer like the rest of the PKs in the system, or is your Invoice PK made up of Cus_PK + OrderDate, while Customer_PK is a 10-byte character field, and InvoiceerDetail PK is made up of Cus_PK + Invoice_PK + OrderDate + LineNo_PK, where LineNo is an integer? To me that is harder to deal with.

The PK is a 32 bit integer field. The only difference between the surrogate and intelligent key here is that the in this case the inv_pk has a meaning outside the RDBMS.

If handling composite key is an issue in your application, you could choose to replace it with a surrogate key.

>I read the paper you cited, and right off the bat I have a question:
>"A child table called order might have an intelligent primary key consisting of two columns: customer_number to act as a foreign key pointer to the customer table plus order_date_time to identify different orders for one customer."

>What do you do when you get two orders from the same customer at the same time. It is not going to happen very often, but when it does, what do you do?

The chance that it would happen is, very, very small, since the datetime datatype has a resolution of one second or 1/100 of a second. If accendently (once in a very long time) it will happen, the primary key uniqueness constrain will let the insert fail. Wether you do or not do handle this situation, the database remains consistant. OTOH, I've got to agree that this solution seems a bit odd to me. I would have choosen for an internal orderno, generated by the system in the same way as surrogate keys.

>Or create a view of DISTINCT city values. When the user enters a city that does not exist in the view, ask them if they typed is correct. In your scenario, how do you handle a new city being entered?

There is a difference in the two approuches: The DISTINCT approach would only recognize cities that are already used in the adress table. The lookup table would allow you to enter all cities at forehand (I know this is a hell lot of more work in the USA than in my small country) and does not allow the users to enter a city that does not occur in the lookup table.

IMO, this situation is not an academic one, since our PPT delivers a postal code table (updated regulary) in which the postalcode automaticly defines the Street and City. In this table the postalcode is the primary key. This allows your application to always have the right adress in your table. If not other PK is available than the postalcode it is harder to deal with surrogate keys here.

>>VFP does not handle RI natively also, but this is not a valid argument to dismiss it (nor it was in FP 2.x for that matter, though it had to be programmed in screens or PRGs). The point is, that this issue is independend of the (R)DBMS used. Further I don't believe that using surrogate keys would dismiss you from writing triggers to force RI or other business rules to create a well designed database.

>We are arguing in circles here.

Yeah, I think so, we seem not to able to catch eachother point.

>>Again, I don't see the point of consistency. Let's take the example of cascading deletes. You've got to implement them in a different way for each (R)DBMS. This also applies to some business rules you want to implement in the database. The choice of using surrogate or intelligent keys do not change this fact, and does not matter also as long as the external scheme is the same for each different (R)DBMS.

>I am not working just on the external scheme, but on the internal scheme as well. I have made this point more than a few times in my previous posts.

The point I'm trying to make is that, I don't care about the internal scheme: If you've got to do more work to implement cascading deletes and/or updates, so be it. The point is that the external schemes of two the same databases in two different DBMSs should be the same. How you handle this in the internal scheme is not a valuable issue whether to use one over the other strategy.

>>Your SQL server may not allow this, but AFAIK there are others who do not have this mechanism (VFP).
>
>I setup my SQL Server database to not allow this by using foreign key constraints.

I'm not talking about a specific implementtation for a DBMS. When handling native VFP data, you'll have to use the RI builder to set the same constraint. Again, the external scheme should be the same, How you handle that internally is not a valid argument. SQL server might do this automaticly, in VFP you've got to do a little more to set the same constraint.

>>See above, though the (R)DBMSs are different the external schemes should be the same. This has nothing to do with the choice between surrogate and intelligent keys. The consistency should lie in the external schemes of the the databases, not in the internal ones. Having two different (R)DBMSs with two different external schemes is not what I call consistency.

>My external scheme is much more consistent than yours.

No, the external schemes (Tables, fields, datatypes, RI rules and other constraints, SQL) should be exacly the same no matter which DMBS used. (Yes, I know that certain DBMS do not support certain data types and there are differences in the SQL language, but at least try to be as uniform as possible).

If using intelligent keys and updating a spefic atricle, it should apply the same RI rules (Ignore, Cascade or Restrict) no matter what DBMS is used.

>>I don't know if you're getting a step closer: As I replied to Erik, you still have to put some constraints on the intelligent alternate key. Though, you've substituted it with a surrogate key, you'll have to ensure its uniqueness (probably by declaring it as candidate or alternate) and may have to define additional business rules to ensure that for example "an articleno may not change when it occurs in the sales table".

>It is not a problem to add unique constraints.

And what about the the "an articleno may not change when it occurs in the sales table" (restrict rule). When the articleno is not the PK anymore (since you've added an alternate key) you can't use an RI rule since the Articleno does not occur in the sales table anymore. You'll have to write a trigger for that.

>>The point was that in some (R)DBMS you could change the surrogate key without having a RI check for related tables. If not, you'll have to add one, or else the is a potential problem your database to get inconsistent.

>Which DBMSs are you talking about?

At least for VFP, I do not know for other DBMSs for sure. Maybe someone else could bring up one or two more.

>No, not with SQL Server 7. You can only have one server, SQL Server or MSDE, on one machine. I think this changes with SQL Server 2000, but I am not working with SQL Server 2000. < g >

What's a problem by using a second machine, when handling such exceptional cases ?

>>INSERT INTO NewTable FROM ;
>>SELECT * FROM oldtable WHERE old_pk NOT IN (SELECT new_pk FROM NewTable)

>It's a little trickier than that. One, your data is in separate databases, right? Or at least on different machines.

My preferred strategy would be to gather all the needed data in VFP and export it to the new database.

>>>Now, what about the old company, the first "ACME001" that was enter 10 years ago. With your statement above, that customer record does not get restored.

>>So how this is not a problem when using surrogate keys ? You'll have to make sure you're alternate key is also unique (after all your user select the customer by its alternate key), I've still have to come accros companies which don't number their customers for other purposes also (e.g. administration, bookkeeping). With surrogate keys the problem might even be worse, because if no uniqueness constraint exists on the alternated key you'll have two customers with the same alternate key without the database giving a sign this happend. How does the user know which one to choose ?

>This is your scenario, how easy it is to restore data. You are helping me make my point that it is not, with either solution (intelligent or surrogate keys).

As I already stated, I agree that restoring a partial backup is a daunting task.

>See my point above about restoringthe old "ACME001" customer record.

As stated above this still is a problem with surrogate keys.

>That was my original point, that restoring backups is not an easy task, so I generally don't delete data. Therefore, I do not <g>generally have to worry about cascading deletes. And with surrogate keys, I don't have to worry about cascading updates.

>I think you are making my points for me. < g >

I don't know if I do, and for which argument. I still find it odd that you don't delete data in a database (and i think most of us would agree). I think that there are other alternatives to this issue. I would have a hard time to imagine one case where I would not want to delete data in a whole database.

Walter,
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform