November 19, 2015 at 7:52 am
I was more thinking of the "main" tables like Customer, Contact, Invoice. No duplication issues there.
And yes, I agree that there is still work to be done for "reference" tables such as a list of states, currencies, etc. These would definitely have to be de-duplicated and the foreign keys would have to be modified for one of the two databases prior to merging it with the other.
Regards
November 19, 2015 at 7:59 am
j-1064772 (11/19/2015)
I was more thinking of the "main" tables like Customer, Contact, Invoice. No duplication issues there.And yes, I agree that there is still work to be done for "reference" tables such as a list of states, currencies, etc. These would definitely have to be de-duplicated and the foreign keys would have to be modified for one of the two databases prior to merging it with the other.
Regards
I would hope that for lookup tables like states and currencies you don't use a guid as the primary key. The ANSI abbreviation is an excellent choice for tables like those. States (assuming US states) would be char(2), there is no need for a guid here as there is a perfect natural key.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2015 at 8:04 am
Good call on that one!
November 19, 2015 at 1:48 pm
j-1064772 (11/19/2015)
I was more thinking of the "main" tables like Customer, Contact, Invoice. No duplication issues there.And yes, I agree that there is still work to be done for "reference" tables such as a list of states, currencies, etc. These would definitely have to be de-duplicated and the foreign keys would have to be modified for one of the two databases prior to merging it with the other.
Regards
Are you sure about Customer, Contact?
Do you really believe the same person/business could not be a customer of both businesses which are being merged?
I would not put my house on that. Would you?
As for orders and invoices - by the time of the executive decision to merge databases is made they usually have already exchanged some information, including orders and invoices.
So, you still need to filter merged invoices using their natural keys, not meaningless GUIDs.
_____________
Code for TallyGenerator
November 20, 2015 at 2:09 am
Sean Lange (11/19/2015)
I would hope that for lookup tables like states and currencies you don't use a guid as the primary key. The ANSI abbreviation is an excellent choice for tables like those. States (assuming US states) would be char(2), there is no need for a guid here as there is a perfect natural key.
It seems like you've never been involved in developing of applications for international customers. When names have to look differently for different users. And sometimes you can't even tell which language should be chosen as primary. 🙂
_____________
Code for TallyGenerator
November 20, 2015 at 3:04 am
Sergiy (11/20/2015)
Sean Lange (11/19/2015)
I would hope that for lookup tables like states and currencies you don't use a guid as the primary key. The ANSI abbreviation is an excellent choice for tables like those. States (assuming US states) would be char(2), there is no need for a guid here as there is a perfect natural key.
It seems like you've never been involved in developing of applications for international customers. When names have to look differently for different users. And sometimes you can't even tell which language should be chosen as primary. 🙂
Hnnnh? We're talking about entities such as currencies and US states that have internationally recognised abbreviations, which can be used as the primary key. So, if we're talking about the Peruvian currency, you can refer to it as "Peruvian Nuevo Sol" or "Nuevo Sol Peruano" (apologies if my Spanish is a bit inaccurate). Those names are just attributes, though - the primary key will still be "PEN".
John
November 20, 2015 at 4:12 am
You know, not everywhere on Earth people use Western encoding of Latin alphabet.
_____________
Code for TallyGenerator
November 20, 2015 at 4:35 am
Good point. Wouldn't it be best, though, to use an international standard where it exists, even if it's in a different alphabet from the rest of the database? After all, if you use GUIDs, they're full of Roman characters anyway.
John
November 20, 2015 at 7:14 am
Sergiy (11/20/2015)
Sean Lange (11/19/2015)
I would hope that for lookup tables like states and currencies you don't use a guid as the primary key. The ANSI abbreviation is an excellent choice for tables like those. States (assuming US states) would be char(2), there is no need for a guid here as there is a perfect natural key.
It seems like you've never been involved in developing of applications for international customers. When names have to look differently for different users. And sometimes you can't even tell which language should be chosen as primary. 🙂
I did say "assuming US states". And of course if the local language is something different then by all means use that language or encoding. It doesn't really matter because this was discussing keys, not what is presented to the end user which can be in any language or character set.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2015 at 1:11 pm
John Mitchell-245523 (11/20/2015)
Good point. Wouldn't it be best, though, to use an international standard where it exists, even if it's in a different alphabet from the rest of the database? After all, if you use GUIDs, they're full of Roman characters anyway.John
First - GIUD is not made of characters. It's a 16 bytes long binary value.
Second - international standards are very conditional, you know. They change from place to place.
And third - US states abbreviations are definitely not an international standard. "CA" - what is it? California? Or Canada?
_____________
Code for TallyGenerator
November 20, 2015 at 1:21 pm
Sergiy (11/20/2015)
And third - US states abbreviations are definitely not an international standard. "CA" - what is it? California? Or Canada?
That is just absurd. There is no standard that says an abbreviation for a country can't also be used for a US state. US state abbreviations are absolutely an international standard. The US state abbreviation for California is always CA and the ISO Country Code for Canada is always CA or CAN (depending on 2 or 3 character code).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2015 at 1:36 pm
<removed> Mistaken.
-- Itzik Ben-Gan 2001
November 20, 2015 at 4:47 pm
Sean Lange (11/20/2015)
Sergiy (11/20/2015)
And third - US states abbreviations are definitely not an international standard. "CA" - what is it? California? Or Canada?
That is just absurd. There is no standard that says an abbreviation for a country can't also be used for a US state. US state abbreviations are absolutely an international standard. The US state abbreviation for California is always CA and the ISO Country Code for Canada is always CA or CAN (depending on 2 or 3 character code).
You know, UPS operates in Russia too.
And they take parcels from Russian customers to California time to time.
Strangely enough, Russian customers are not quite aware of the "international standard" for the abbreviation and almost never use it.
So, the software UPS use in Russia would have a problem recognising the address if it would rely on that "international standard" as a natural key.
_____________
Code for TallyGenerator
November 21, 2015 at 2:46 am
Sergiy (11/20/2015)
First - GIUD is not made of characters. It's a 16 bytes long binary value.
I thought you'd say that. You're right, but it's still rendered as a series of letters and numbers, the same way as PEN and GBP and USD and RUB are just a series of letters. You only have to set them up once and the user doesn't need to see them.
So, the software UPS use in Russia would have a problem recognising the address if it would rely on that "international standard" as a natural key.
You'd think that if you were going to the trouble of setting up a table of US states that you'd also go to the trouble of finding out what the official abbreviations are. Like I said, you only have to do it once and it's transparent to the user. But let's accept and agree that there will be edge cases where some people will use what they consider to be an (inter)national standard, where others will take the view that there is no standard and will instead use ID, GUID or some other kind of primary key.
John
November 21, 2015 at 4:51 am
John Mitchell-245523 (11/21/2015)
Sergiy (11/20/2015)
First - GIUD is not made of characters. It's a 16 bytes long binary value.I thought you'd say that. You're right, but it's still rendered as a series of letters and numbers, the same way as PEN and GBP and USD and RUB are just a series of letters. You only have to set them up once and the user doesn't need to see them.
Rendering is just what it is - rendering for your eyes.
Those series of letters you see on the screen as GUID's are not stored in database, like series of letters representing currenciers.
Unless, of course, if some genius decides to store GUID as char(36), not uniqueidentifier.
Same thing as with dates: same date may be rendered to different character strings, and same string may be representing different dates, depending on application setting, localisation, etc.
BTW, same may be said about numbers: 31OCT = 25DEC.
So, your comparison here is not valid.
You'd think that if you were going to the trouble of setting up a table of US states that you'd also go to the trouble of finding out what the official abbreviations are. Like I said, you only have to do it once and it's transparent to the user. But let's accept and agree that there will be edge cases where some people will use what they consider to be an (inter)national standard, where others will take the view that there is no standard and will instead use ID, GUID or some other kind of primary key.
John
Those cases may be "edge" for US residents.
They are mainstream for everyone outside of US.
You guys use to watch "World Series". The name implies that it has something to do with the world.
But believe me, most of the people outside of US would have trouble to tell which sport play the participants of the World Series.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply