eCharcha.Com   Support eCharcha.Com. Click on sponsor ad to shop online!

Advertise Here

Go Back   eCharcha.Com > Science and Technology > Computing

Notices

Computing All things binary...

Reply
 
Thread Tools Display Modes
  #1  
Old September 19th, 2003, 08:30 AM
Thorn's Avatar
Thorn Thorn is offline
Junior eCharchan
 
Join Date: Jun 2001
Posts: 81
Thorn is on a distinguished road
Oracle DB design: best practices...

What are the best practices in designing databases in Oracle as what the primary key should be:
#1 The ID (primary key) to be a numeric sequence number
#2 The ID (primary key) to be a varchar2(32) with GUID values in it

I just don't want what should be used, but I want details on "why?" and also the impact on performance, security and extendability. The database is with a minimum of 100 tables on an average of 1000 rows in each and some of them have a growth rate of 10-20% per week.
Reply With Quote
  #2  
Old September 25th, 2003, 10:06 AM
wmac's Avatar
wmac wmac is offline
eCharchan
 
Join Date: Jan 2003
Location: Iran - Tehran
Posts: 395
wmac is on a distinguished road
Hello,

I personally use method #1 and I use that ID field everywhere.

Though I do not use Oracle very much (mostly MySQL, DB2 and MSSQL).

Regards,
Mac
Reply With Quote
  #3  
Old September 25th, 2003, 11:05 AM
Diplomat's Avatar
Diplomat Diplomat is offline
Senior eCharchan
 
Join Date: May 2002
Posts: 5,293
Diplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond repute
The application dictates what your primary key should be.

That is the only rule....i follow.
__________________
With Freedom comes Responsibility.
With Responsibility comes Freedom - Indian govt officials

Reply With Quote
  #4  
Old September 26th, 2003, 08:09 AM
loverboy260's Avatar
loverboy260 loverboy260 is offline
A proud Muslim FULLSTOP!
 
Join Date: May 2001
Location: England
Posts: 1,190
loverboy260 is infamous around these partsloverboy260 is infamous around these partsloverboy260 is infamous around these partsloverboy260 is infamous around these partsloverboy260 is infamous around these partsloverboy260 is infamous around these parts
varchar2(32)
why do we use varchar2 why not varchar3 or 1? can anyone explain please?
and i am learning oracle in my course so shud be fun!
Reply With Quote
  #5  
Old September 26th, 2003, 09:53 AM
Big-G's Avatar
Big-G Big-G is offline
Senior eCharchan
 
Join Date: Mar 2001
Posts: 11,578
Big-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud of
Quote:
Originally posted by loverboy260
varchar2(32)
why do we use varchar2 why not varchar3 or 1? can anyone explain please?
and i am learning oracle in my course so shud be fun!
Varchar is the older term for specifying the variable character field. This was getting discontinued from oracle 8 or 9 (can't recall). That's why it was recommended that you use varcha2 and not varchar. In future the keyword varchar might be used to represent something else. In the current versions, even if you use varchar, it gets changed to varchar2 after the creation of the field.
__________________
Having a smoking section in a restaurant is like having a pissing section in a pool!
Reply With Quote
  #6  
Old September 26th, 2003, 09:58 AM
ShivSainik's Avatar
ShivSainik ShivSainik is offline
Senior eCharchan
 
Join Date: Apr 2001
Location: San Jose, CA
Posts: 5,253
ShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond repute
Re: Oracle DB design: best practices...

Quote:
Originally posted by Thorn
What are the best practices in designing databases in Oracle as what the primary key should be:
#1 The ID (primary key) to be a numeric sequence number
#2 The ID (primary key) to be a varchar2(32) with GUID values in it

I just don't want what should be used, but I want details on "why?" and also the impact on performance, security and extendability. The database is with a minimum of 100 tables on an average of 1000 rows in each and some of them have a growth rate of 10-20% per week.
I am not expert of Oracle or any databases. But I have done a project called "Object store". From that experience I can say that performance wise varchar(32) will be costly of course, because it will be kind of strcmp(), so 32 machine instructions.......and on the other hand integer comparison is just 1 machine inst.

But again you can create intelligent indexes to avoid all this.
__________________
"Once you start thinking too much about your place in history, you're at a point where you're no longer trying as hard as you should be"
Reply With Quote
  #7  
Old September 26th, 2003, 10:18 AM
Big-G's Avatar
Big-G Big-G is offline
Senior eCharchan
 
Join Date: Mar 2001
Posts: 11,578
Big-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud of
Quote:
Originally posted by Thorn
What are the best practices in designing databases in Oracle as what the primary key should be:
#1 The ID (primary key) to be a numeric sequence number
#2 The ID (primary key) to be a varchar2(32) with GUID values in it

I just don't want what should be used, but I want details on "why?" and also the impact on performance, security and extendability. The database is with a minimum of 100 tables on an average of 1000 rows in each and some of them have a growth rate of 10-20% per week.
I am not a DB guy, but from my limited knowledge, for the primary key, either numeric sequence or varchar(2) would make any noticable difference. Numeric ones might be sllightly better in terms of data storage (we are talking at the kernel level) but for a database as small as yours. 1000 rows per table is hardly anything. It would not really matter. I would go with a numeric sequence.

However, and I am sure you know it, for performance benefits, proper indexing should help. In fact, regarding primary keys, I have normally avoided assigning a field as a primary key. But I rather create a UNIQUE INDEX on the column I need as a primary key. Serves both the purposes of having unique identifiers in the PK column, as well as creates index on that column as usually PK will always be there in the WHERE clause of a query on that table.
__________________
Having a smoking section in a restaurant is like having a pissing section in a pool!
Reply With Quote
  #8  
Old September 26th, 2003, 10:19 AM
Big-G's Avatar
Big-G Big-G is offline
Senior eCharchan
 
Join Date: Mar 2001
Posts: 11,578
Big-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud of
Quote:
Originally posted by Diplomat
The application dictates what your primary key should be.

That is the only rule....i follow.
On the contrary, the functionality of the application should have nothing to do with the primary key. Primary keys, as far as visibility from the application level is considered, are invisible.
__________________
Having a smoking section in a restaurant is like having a pissing section in a pool!
Reply With Quote
  #9  
Old September 26th, 2003, 10:32 AM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,951
echarcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond repute
I am no DB expert

but looking at large applications and also commercial software like vBulletin, I have noticed that integers are used for Primary Key. For example, every user on eCharcha has a userid which is purely numerical.

I know that many sites like www.webmd.com use a GUID (which is a Microsoft COM related concept and also available in CORBA) for a primary key. Well, it all depends. If you foresee hunderds of thousands of records in the future then it would be best to use numbers only and not GUIDs which are alpha-numeric in nature and basically strings or character arrays at the lowest kernel level.

But today the servers have massive amounts of memory plus multiple processors running in GigaHertz range. So a few extra computations for comparing strings should not be a problem.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #10  
Old September 26th, 2003, 12:27 PM
Diplomat's Avatar
Diplomat Diplomat is offline
Senior eCharchan
 
Join Date: May 2002
Posts: 5,293
Diplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond reputeDiplomat has a reputation beyond repute
Quote:
Originally posted by Big-G
On the contrary, the functionality of the application should have nothing to do with the primary key. Primary keys, as far as visibility from the application level is considered, are invisible.
not where I work.

The primary key is the unique key to the record and creates a primary index at the same time you created the primary key, by default.
__________________
With Freedom comes Responsibility.
With Responsibility comes Freedom - Indian govt officials

Reply With Quote
  #11  
Old September 26th, 2003, 01:07 PM
Big-G's Avatar
Big-G Big-G is offline
Senior eCharchan
 
Join Date: Mar 2001
Posts: 11,578
Big-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud ofBig-G has much to be proud of
I take that back.
__________________
Having a smoking section in a restaurant is like having a pissing section in a pool!

Last edited by Big-G; September 26th, 2003 at 01:19 PM.
Reply With Quote
  #12  
Old September 26th, 2003, 04:20 PM
GunsNRoses's Avatar
GunsNRoses GunsNRoses is offline
Spear Barbarian
 
Join Date: Jun 2001
Posts: 1,263
GunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud of
>> #2 The ID (primary key) to be a varchar2(32) with GUID values in it
You HAVE to do this, if you're planning to do merge-replication. What this means is that you have a master server that pulls the data from different slave servers and combines it all into one big dataset and then pushes out the changes to all the slaves. Thus, all the slaves will have their data synchronized with each other. You cannot use numeric sequence numbers, otherwise two slave servers might generate the same sequence number, so if you're using merge replication, then GUIDs are your solution. Merge replication will come in handy if you have load balanced SQL servers, because changes made to one server need to be propogated to the other ones quickly.

Most of the time at work, we don't use merge-replication, but we've recently found some need to. The trouble is that MS SQL's merge replication is a little trickier than your simple push type deal that we use right now (i.e. we make changes only on the master server and push them all out to slaves) and our DBAs are a bit reluctant to implement that now, because we want to measure the performance on our existing hardware first. We have enough problems with our master server going down on us every couple of weeks already, cuz of outdated sh*tty hardware.
__________________
"Pick up your balls and load up your cannon
For a twenty-one gun salute."
-- For Those About To Rock (We Salute You)

Proud LLKC member since May 2002.
"We've been looking for the enemy for some time now. We've finally found him. We're surrounded. That simplifies things." Lt. Gen. Lewis "Chesty" Puller, USMC, Chosin Reservoir 1950

Reply With Quote
  #13  
Old September 26th, 2003, 04:44 PM
YedaAnna's Avatar
YedaAnna YedaAnna is offline
Livin La Vida Loca
 
Join Date: Aug 2002
Location: eCharcha!!!
Posts: 5,713
YedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond repute
GUIDs are not advisable for public databases (like websites and databases that involve information off of comps of general public) due to privacy issues. MS took back their MS Office version that used to record GUIDs in Word documents to track genuine users after several privacy litigations. You can check that by creating a Word doc in Word 97 and opening it using notepad. Search for GUID and try to compare it with ur MAC Address (ipconfig/all on DOS cmd prmpt).

Also it is more logical to have sequence numbers in the Primary Key field (more useful in querying) rather than characters (more resources generally). But again as GunsBhai said when u r merging DBs they might clash. There shud be a mechanism to workaround this (say create additional fields to note database names).

the application shudnt dictate the usage of primary keys. Every record is unique and so is every primary key. the aim is to create uniqueness
Reply With Quote
  #14  
Old September 26th, 2003, 07:56 PM
GunsNRoses's Avatar
GunsNRoses GunsNRoses is offline
Spear Barbarian
 
Join Date: Jun 2001
Posts: 1,263
GunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud ofGunsNRoses has much to be proud of
Yedabhai, GUID stands for Globally Unique IDentifier. There are many different ways to generate a GUID and not all the uses are bad.
>>MS took back their MS Office version that used to record GUIDs in Word documents to track genuine users after several privacy litigations.
This was because somebody at microsoft was dumb enough to use the network card's MAC address unencryped as part of the GUID. It wasn't such a bad idea to use the MAC address of a machine, since every network card in the world has its own unique MAC address (this is defined by IEEE standards). The stupid thing was to include the MAC address in as-is -- if I recall correctly SQL Server 7.0 had the same issue for GUID columns. Anyways, the next version of SQL Server uses the MAC as part of the GUID too, but it always hashes it first, so the resulting GUID had nothing in common with the MAC address. It is also impossible to find out the MAC address of a machine from the resulting GUID since the hashing algorithm is one way. Also, in SQL Server (and any other DB engine), the MAC address used to generate the GUID is the MAC address of the server, not your client computer --- so your privacy is safe.

>> There shud be a mechanism to workaround this (say create additional fields to note database names).
When you're using merge replication, the database names are usually the same --- too much hassle to call the database on each machine a different name, if you're going to have the same data in all of them. Since MAC addresses are unique per card, it isn't such a bad idea to use it as part of the GUID field .

Of course, like you said, it is a lot faster to use numbers that char strings as primary key. The indexing is faster and lookups are much faster this way. However, you can't guarantee that you won't have key clashes this way, whereas with a GUID, the chances are a lot lesser .

For the record, we use option #1 (i.e. sequence numbers, autoincrement, identity field or what have you) in 99.99% of our tables and are only investigating GUIDs for a future project which may require merge replication . So if you had to ask me, I would say we use option #1.
__________________
"Pick up your balls and load up your cannon
For a twenty-one gun salute."
-- For Those About To Rock (We Salute You)

Proud LLKC member since May 2002.
"We've been looking for the enemy for some time now. We've finally found him. We're surrounded. That simplifies things." Lt. Gen. Lewis "Chesty" Puller, USMC, Chosin Reservoir 1950

Reply With Quote
  #15  
Old September 26th, 2003, 08:13 PM
YedaAnna's Avatar
YedaAnna YedaAnna is offline
Livin La Vida Loca
 
Join Date: Aug 2002
Location: eCharcha!!!
Posts: 5,713
YedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond reputeYedaAnna has a reputation beyond repute
Quote:
Originally posted by GunsNRoses
Yedabhai, GUID stands for Globally Unique IDentifier. There are many different ways to generate a GUID and not all the uses are bad.
>>MS took back their MS Office version that used to record GUIDs in Word documents to track genuine users after several privacy litigations.
This was .....
Yup Guns you right abt MS putting it as-is rather than hashing it. Even an MD5 would have done that. I was refering to the overall option of creating the Primary key for userids etc. for web registration and other small-time dbs from MAC addresses. Again now that they are runnin outta these addresses, manufacturers are shipping sets of NICs from the same batch having identical MACs to different parts of the world so that they dont clash in the same geographic region and if they do, make use of the IP.

Abt the merging yeah depends on the databases, relating them to their originals might turn out to be a nightmare later..

I usually try to stick to the basics

The GUID scheme might turn out to be promising
Reply With Quote
Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
New bra design echarcha Friday Special 7 September 15th, 2009 01:24 PM
Indian Doctor's company prospers by questionable medical/business practices Ravi Life Abroad 11 July 28th, 2007 06:33 PM
What coding practices do you follow? YedaAnna Computing 8 October 30th, 2003 12:01 PM
Please help me design a web site YedaAnna SoapBox 28 May 1st, 2003 05:09 PM
Videsee.com - design smartganduinuk SoapBox 23 August 29th, 2001 11:04 AM


All times are GMT -7. The time now is 11:47 AM.


Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Site Copyright © eCharcha.Com 2000-2012.