SEE: http://support.microsoft.com/defaul...kb;en-us;815154
I get through this article and come to a dead stop not understanding
the best way to implement Step 14. Click (and apply) Permissions
to the objects in the database.
Do I as the admin have to check each and every object one at a time?
Do I as the admin have to do all of these steps to each and every database
I would want to configure for access by ASP.NET applications?
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee
.com
URL http://www.metromilwaukee.com/clintongallagher/> Do I as the admin have to check each and every object one at a time?
> Do I as the admin have to do all of these steps to each and every database
> I would want to configure for access by ASP.NET applications?
Best practices dictate that you should grant permissions on only those
objects needed by your application. SQL Server doesn't know which objects
your application references nor what permissions (SELECT, UPDATE, etc.) are
required. Consequently, there is no real shortcut for the task.
If your application reads from *all* tables and views directly, you can add
the user to the fixed db_datareader database role. If your application
writes to *all* tables and views, you can add the user to the fixed
db_datarwriter database role. You'll still need to assign stored procedure
execute permissions individually since there is currently no fixed database
role for this.
Your admin might find it easier execute permission scripts using Query
Analyzer rather than the Enterprise Manager if you have a lot of
objects/databases. The script below will generate GRANT statements for all
database objects. You can tweak the script to fit your needs and/or remote
unneeded GRANTs from the generated script.
SET NOCOUNT ON
DECLARE @.Permissions TABLE (Permission nvarchar(10))
INSERT INTO @.Permissions VALUES('SELECT')
INSERT INTO @.Permissions VALUES('INSERT')
INSERT INTO @.Permissions VALUES('UPDATE')
INSERT INTO @.Permissions VALUES('DELETE')
INSERT INTO @.Permissions VALUES('EXECUTE')
SELECT
N'GRANT ' +
[p].[Permission] +
N' ON ' +
QUOTENAME(USER_NAME([o].[uid])) +
N'.' +
QUOTENAME([o].[name]) +
N' TO MyUser'
FROM sysobjects o
CROSS JOIN @.Permissions p
WHERE
OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
((OBJECTPROPERTY([o].[id], 'IsProcedure') = 1 AND
[p].[Permission] = N'EXECUTE') OR
(OBJECTPROPERTY([o].[id], 'IsUserTable') = 1 AND
[p].[Permission] <> N'EXECUTE') OR
(OBJECTPROPERTY([o].[id], 'IsView') = 1 AND
[p].[Permission] <> N'EXECUTE') OR
(OBJECTPROPERTY([o].[id], 'IsTableFunction') = 1 AND
[p].[Permission] <> N'EXECUTE') OR
(OBJECTPROPERTY([o].[id], 'IsInlineFunction') = 1 AND
[p].[Permission] = N'EXECUTE') OR
(OBJECTPROPERTY([o].[id], 'IsScalarFunction') = 1 AND
[p].[Permission] = N'EXECUTE'))
ORDER BY
[p].[Permission],
USER_NAME([o].[uid]),
[o].[name]
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in message
news:OrW5lboJEHA.428@.TK2MSFTNGP11.phx.gbl...
> SEE: http://support.microsoft.com/defaul...kb;en-us;815154
> I get through this article and come to a dead stop not understanding
> the best way to implement Step 14. Click (and apply) Permissions
> to the objects in the database.
> Do I as the admin have to check each and every object one at a time?
> Do I as the admin have to do all of these steps to each and every database
> I would want to configure for access by ASP.NET applications?
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@.REMOVETHISTEXTmetromilwaukee
.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes, your comments helped Dan, thank you. I didn't know the
db_ roles were inclusive in this context and I'll find the script very
useful.
I'm planning to include SQL Server as one of my MCAD electives
but for now remain quite clueless about how to resolve security issues.
For example, why my ASP.NET code can connect to the SQL Server
when using Integrated Security or sa but not when using any other user id
despite the presence of a SQL Server user I thought I had created
correctly.
In fact, I'm starting to understand the entire security model on my
development machine is probably FUBAR as my ASP.NET
applications all seem to run as the NT Authority rather than via the
ASPNET worker process.
Are there any papers you could recommend I read that document
what may be a base security configuration?
<%= Clinton Gallagher
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OkePvntJEHA.2376@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> Best practices dictate that you should grant permissions on only those
> objects needed by your application. SQL Server doesn't know which objects
> your application references nor what permissions (SELECT, UPDATE, etc.)
are
> required. Consequently, there is no real shortcut for the task.
> If your application reads from *all* tables and views directly, you can
add
> the user to the fixed db_datareader database role. If your application
> writes to *all* tables and views, you can add the user to the fixed
> db_datarwriter database role. You'll still need to assign stored
procedure
> execute permissions individually since there is currently no fixed
database
> role for this.
> Your admin might find it easier execute permission scripts using Query
> Analyzer rather than the Enterprise Manager if you have a lot of
> objects/databases. The script below will generate GRANT statements for
all
> database objects. You can tweak the script to fit your needs and/or
remote
> unneeded GRANTs from the generated script.
> SET NOCOUNT ON
> DECLARE @.Permissions TABLE (Permission nvarchar(10))
> INSERT INTO @.Permissions VALUES('SELECT')
> INSERT INTO @.Permissions VALUES('INSERT')
> INSERT INTO @.Permissions VALUES('UPDATE')
> INSERT INTO @.Permissions VALUES('DELETE')
> INSERT INTO @.Permissions VALUES('EXECUTE')
> SELECT
> N'GRANT ' +
> [p].[Permission] +
> N' ON ' +
> QUOTENAME(USER_NAME([o].[uid])) +
> N'.' +
> QUOTENAME([o].[name]) +
> N' TO MyUser'
> FROM sysobjects o
> CROSS JOIN @.Permissions p
> WHERE
> OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
> ((OBJECTPROPERTY([o].[id], 'IsProcedure') = 1 AND
> [p].[Permission] = N'EXECUTE') OR
> (OBJECTPROPERTY([o].[id], 'IsUserTable') = 1 AND
> [p].[Permission] <> N'EXECUTE') OR
> (OBJECTPROPERTY([o].[id], 'IsView') = 1 AND
> [p].[Permission] <> N'EXECUTE') OR
> (OBJECTPROPERTY([o].[id], 'IsTableFunction') = 1 AND
> [p].[Permission] <> N'EXECUTE') OR
> (OBJECTPROPERTY([o].[id], 'IsInlineFunction') = 1 AND
> [p].[Permission] = N'EXECUTE') OR
> (OBJECTPROPERTY([o].[id], 'IsScalarFunction') = 1 AND
> [p].[Permission] = N'EXECUTE'))
> ORDER BY
> [p].[Permission],
> USER_NAME([o].[uid]),
> [o].[name]
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in
message
> news:OrW5lboJEHA.428@.TK2MSFTNGP11.phx.gbl...
database[vbcol=seagreen]
>|||> Are there any papers you could recommend I read that document
> what may be a base security configuration?
Check out Building Secure ASP.NET Applications
<http://msdn.microsoft.com/library/d...n-us/dnnetsec/h
tml/secnetlpMSDN.asp?frame=true>.
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in message
news:OLt8MuvJEHA.3216@.tk2msftngp13.phx.gbl...
> Yes, your comments helped Dan, thank you. I didn't know the
> db_ roles were inclusive in this context and I'll find the script very
> useful.
> I'm planning to include SQL Server as one of my MCAD electives
> but for now remain quite clueless about how to resolve security issues.
> For example, why my ASP.NET code can connect to the SQL Server
> when using Integrated Security or sa but not when using any other user id
> despite the presence of a SQL Server user I thought I had created
> correctly.
> In fact, I'm starting to understand the entire security model on my
> development machine is probably FUBAR as my ASP.NET
> applications all seem to run as the NT Authority rather than via the
> ASPNET worker process.
> Are there any papers you could recommend I read that document
> what may be a base security configuration?
> <%= Clinton Gallagher
>
>
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OkePvntJEHA.2376@.tk2msftngp13.phx.gbl...
> database
objects[vbcol=seagreen]
> are
> add
> procedure
> database
> all
> remote
> message
> database
>|||That article looks good. Thanks again Dan.
<%= Clinton Gallagher
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:enSrNK0JEHA.4072@.TK2MSFTNGP12.phx.gbl...
> Check out Building Secure ASP.NET Applications
>
<http://msdn.microsoft.com/library/d...n-us/dnnetsec/h
> tml/secnetlpMSDN.asp?frame=true>.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in
message
> news:OLt8MuvJEHA.3216@.tk2msftngp13.phx.gbl...
id[vbcol=seagreen]
> objects
etc.)[vbcol=seagreen]
can[vbcol=seagreen]
for[vbcol=seagreen]
Development[vbcol=seagreen]
>
Showing posts with label http. Show all posts
Showing posts with label http. Show all posts
Friday, March 30, 2012
Monday, March 12, 2012
Key fields
Edit: the question is more clear in http://www.dbforums.com/showthread.php?p=4477661&posted=1#post4477661
OK this question is not really SQL server related but since we will build the database in MS SQL Server (2000) i think this is the proper forum to ask this question.
It's actually a quite simple question i gues, but my experience with databases is very limited (buiding my first serious database actually).
when i have two tables Organization and Samples coming from this organization with the following fields:
Organization:
InstituteID (identification)
Name
Adress
...
Sample:
SampleID (sample identification)
InstituteID (references to the Institute table)
SampleDate
Location
...
In general is it better to use the sampleID and InstituteID combined as primary key in the second (sample) table (and build an index on the combined key) or to generate a new unique key for each record? And why should i choose either one of these options?
One advantage of the first option is you dont have to generate keys yourself (or use some autonumbering system), however the disadvantage is the key grows bigger and bigger when adding more tables (fraction taken from samples, analysis performed on fractions, Analysis giving results).This is an aged old question/debate. Artificial key allows you to have a narrow key but natural key allows you to cover the query. Depending on who you ask, the opinion and supporting *proof* in perf gain can be shown.
Personally, I would go with natural key but I do use artificial key in the past.
Here is a sample of previous discussion on such topic.
http://groups.google.com/groups?q=sqlserver+artificial+key+vs+natural+key|||I am already reading a lot about it so ill hope i can decide for myself but still any opinions are appreciated. One more thing that might be important is that when using natural keys in deeper tables (such as results) a natural key can result in up to 7 fields to make a record unique.
Edit: Another thing to consider: It is most likely because of the complexity of data checking (not done by the DBMS) needed before records are inserted most of the data will be inserted by a single administrator executing these procedures.|||One more question:
If i have say a number of countries (max length 20 characters) should i put them in a seperate table (they dont have aditional attributes) and use integer numbers to refer to this table or just store the full country name in the field? If i do add them in a different table you haev to generate id's for them and need 1 more join to join the data. On the other hand you do save space in the database, but is it really worth the hassle?|||If this is an OLTP system, the normalization should be observed. Thus, a seperate table for COUNTRIES would be needed.
As to your earlier question about the wide multi-column key, I would suggest you search for Kimberly Tripp's article/paper on covering indexes.|||ok im really a starter so why would that be? For data inserts/updates it will take longer and use more space if not normaliing countries, but for data retreival joining another table will make the time to retreive results longer not?|||I'm confused. Is SampleID unique for each Sample record, as its name implies? If so, then under no circumstances would you want to combine it with InstituteID as a composite key. It is a primary key on its own.
If SampleID is not unique for each Sample record, then you might want to rethink your object names. If the Sample record is simply implementing a many-to-many join between Institutes and some other sample table, then an additional surrogate key may not be necessary, but may simplify application programming.
I am one of the camp that puts a surrogate key on everything, because composite keys become unwieldy after several layers of day, and I believe that consistency in development is important.|||Different institutes supply unique Samples. Though the SampleID institute 1 gives to a sample can be the same as intitute 2 gives to another sample. That is why only instituteID+SampleID make a record unique.
From this sample, fractions are taken, the fractions are analyzed and the analysis gives results (so all 1 to many relations)
if i continue using keys the way i use them in the sample table example i will end up in the results table with the following fields identifying a record:
InstituteID
SampleID
FractionMethodID
AnalysisMethodID
MeaurementNumber
CASNumber (identifying a result)
So the 2 questions i have:
Should i replace this key (build mostly from natural keys) with 1 numeric key value (composite key)? This means more joins to retrieve certain information (all tables have to be included to link results to an institute)? What will work faster?
Or should i replace the natural keys in every table with a composite key and use this composite keys as replacement for natural keys but use them in the same way i use the natural keys now so i am still able to use less joins to retrieve data? (you know what i mean here?)|||It is a matter of choice and style. I make sure every table I create has a non-composite primary key, usually surrogate.
Regardless, you will want a unique index/constraint on InstituteID/SampleID if you elect not to make it the primary key.|||I have read pages and pages about this topic already, but every person, site or newsgroup seems to have a different opinion. That makes it not easier to come to a final decission. Even checking performance afterwards seems no option since at this moment we have no idea about how big the database will grow and this also seems to influences what path to take.
It all comes down to the following 1 question:
What works faster for data RETRIEVAL. Normalize as far as u can go and generate surrogate keys in every table where necessary, resulting in more joins to retrieve data or use natural composite keys that can result in keys up to 7 fields BUT not having to join a lot of tables to get the same result? Expect the database to grow pretty large over time.
If anyone could answer that it would help greatly in coming to a decission about what path to take.|||I think you are trying to make it simpler than it is. You really can't boil it down to that one either-or decision.
Database design is what it is because thought and trial-n-error have brought it to where it is. Complete/Full normalization is ideal if you want to get anything from anywhere, coming from any direction. The reality is that there are/may be tradeoffs to performance in a fully-normalized DB. This accounts for much of the reason there are different LEVELS of normalization.
If you really want speed in the retrieval of data, you perhaps should use an indexed flat file, or a single table with a single unique key. Yeah, going way the other way, but you get the idea.
There is no silver bullet.
Everything's a tradeoff.
Which accounts for the number and varied opinions on the subject.|||I know this is not an easy topic with one streight forward answer. But my problem is that because i am still a rookie with database design i am trying to find out what the things are that are most difficult to do for a DBMS (MS SQL server in this case), before trying to decide with what option i will go (mostly surrogate or natural keys)
I dont expect a clear answer but am hoping that someone who does have experience with the MS SQL server DBMS and who has build a similar database like i do (a database with say six 1 to many related tables and some lookup tables attached to it) could come up with the basic advantages and disadvantages of using surrogate keys in a database similar to mine.
If i have 6 tables and the 6th table uses the primary key of the 5th table + 1 field to make tuples in in this 6th table unique i just have the feeling it is not right since you have a lot of fields in more than 1 table (not properly normalized if u ask me). It feels better to just create 1 surrogate key for each 1 of these fields (which does result in having one surrogate primary key unqiue for a tuple and having a surrogate reference key refering to its parent table.)
But am i right here? Will Select queries run fast in both systems as long as the tables are properly indexed or does 1 of these methods have a sgnificant disadvantage opposed to the other?
I just want to get informed well before i am making a decission based on a feeling that is not right :)|||If the database is expected to be large, you do not want to carry around any extra storage space. Bigger backups, more resources to archive and purge data etc.
It's not a question of all or nothing, it's possible to use natural keys to a certain level in the hierarchy, then at some point assign a surrogate identity for further referencing from that point.
Personally I prefer to use natural keys, but use surrogates when it makes sense to do so.
As for the performance questions you had, I think neither method has a distinct advantage over the other. That depends more on usage patterns of the data (and indexes). But methodwise, properly implemented both are ok.
rockmoose|||So when does it make sense for you to use surrogates?|||After reading a lot i am leaning toward surrogate keys now. Mostly because using a number of colums as primary key and clustering them makes the the indexes larger, which can result in deecreased performance. Next to that a lot more storage is required and updates to data (that won't hapen often though) require a lot of cascade updating.
The only big disadvantage is having to use most tables in every query to filter on certain data. However i have the feeling that when the DBMS is smart enough in query optimization this shoulden't cause a too high performance penalty|||From a performance and technical perspective an ideal key is thin, stable and monotonically increasing. From a usage perspective it also represent a business meaning.
thin - this is particularily important if the key is going to be propagated to large child tables, you don't want to carry the overhead of large (or multiple) column keys.
stable - a changing key is not a good choice of key, changes will have to be propagated throughout the database.
monotonicaly increasing - this is purely for technical and perf reasons, for large tables with many inserts this is important. Not so important for "static" or small tables.
convey business meaning - easier to work with, less lookups/joins necessary.
if a table has surrogate key, you still need a unique constraint to make sure that no duplicate data is inserted.
So when does it make sense to me?
Well, when a surrogate would clearly give more of the above benefits than a natural key would.
When I design a database and introduce a surrogate, I want to be able to justify it.
e.g. "I put a surrogate here, because the natural key is 4 columns and 32 bytes wide, and it is going to be propagated into this 20 mil row transaction table"
Did you read all the links to discussions oj gave you ;) ?!??!
__________________________________
I wish we had a NATURAL JOIN operator
rockmoose|||After reading a lot i am leaning toward surrogate keys now. Mostly because using a number of colums as primary key and clustering them makes the the indexes larger, which can result in deecreased performance. Next to that a lot more storage is required and updates to data (that won't hapen often though) require a lot of cascade updating.
The only big disadvantage is having to use most tables in every query to filter on certain data. However i have the feeling that when the DBMS is smart enough in query optimization this shoulden't cause a too high performance penalty
You can create views that do the joins for you, and query these.
Have a physical storage layer with surrogates, and a logical view layer with more "business meaning".
IMO, when introducing a surrogate key one is introducing an extra layer of indirection. i.e. we tell the system that this surrogate stands for this natural key. And that from this point we have to perform a lookup to access the data.
This should be done judiciously and for a reason.|||Yes i did read those topics, but for a beginning db developper it still makes it not easy to decide what option to choose.
Even if i go for surrogate keys you have the option to propage the key into the child tables (again resulting in multiple column keys but at least limiting the size) or don't do this and generate a new surrogate key in each table where no keys are propagated.
About this last i have read nowhere in any of the topics on the google news groups
Is there even a sugestion for when to go for surrogates? I mean if a key consist of more than X colums or the column length of a key is larger than y characters or bytes (If the key is stable and unique?).
Another thing that might be good to know for me is if a key field is say 20 characters what influence will that have on the index generated on such field? Will it be larger than an index generated on a 5 character field?
OK this question is not really SQL server related but since we will build the database in MS SQL Server (2000) i think this is the proper forum to ask this question.
It's actually a quite simple question i gues, but my experience with databases is very limited (buiding my first serious database actually).
when i have two tables Organization and Samples coming from this organization with the following fields:
Organization:
InstituteID (identification)
Name
Adress
...
Sample:
SampleID (sample identification)
InstituteID (references to the Institute table)
SampleDate
Location
...
In general is it better to use the sampleID and InstituteID combined as primary key in the second (sample) table (and build an index on the combined key) or to generate a new unique key for each record? And why should i choose either one of these options?
One advantage of the first option is you dont have to generate keys yourself (or use some autonumbering system), however the disadvantage is the key grows bigger and bigger when adding more tables (fraction taken from samples, analysis performed on fractions, Analysis giving results).This is an aged old question/debate. Artificial key allows you to have a narrow key but natural key allows you to cover the query. Depending on who you ask, the opinion and supporting *proof* in perf gain can be shown.
Personally, I would go with natural key but I do use artificial key in the past.
Here is a sample of previous discussion on such topic.
http://groups.google.com/groups?q=sqlserver+artificial+key+vs+natural+key|||I am already reading a lot about it so ill hope i can decide for myself but still any opinions are appreciated. One more thing that might be important is that when using natural keys in deeper tables (such as results) a natural key can result in up to 7 fields to make a record unique.
Edit: Another thing to consider: It is most likely because of the complexity of data checking (not done by the DBMS) needed before records are inserted most of the data will be inserted by a single administrator executing these procedures.|||One more question:
If i have say a number of countries (max length 20 characters) should i put them in a seperate table (they dont have aditional attributes) and use integer numbers to refer to this table or just store the full country name in the field? If i do add them in a different table you haev to generate id's for them and need 1 more join to join the data. On the other hand you do save space in the database, but is it really worth the hassle?|||If this is an OLTP system, the normalization should be observed. Thus, a seperate table for COUNTRIES would be needed.
As to your earlier question about the wide multi-column key, I would suggest you search for Kimberly Tripp's article/paper on covering indexes.|||ok im really a starter so why would that be? For data inserts/updates it will take longer and use more space if not normaliing countries, but for data retreival joining another table will make the time to retreive results longer not?|||I'm confused. Is SampleID unique for each Sample record, as its name implies? If so, then under no circumstances would you want to combine it with InstituteID as a composite key. It is a primary key on its own.
If SampleID is not unique for each Sample record, then you might want to rethink your object names. If the Sample record is simply implementing a many-to-many join between Institutes and some other sample table, then an additional surrogate key may not be necessary, but may simplify application programming.
I am one of the camp that puts a surrogate key on everything, because composite keys become unwieldy after several layers of day, and I believe that consistency in development is important.|||Different institutes supply unique Samples. Though the SampleID institute 1 gives to a sample can be the same as intitute 2 gives to another sample. That is why only instituteID+SampleID make a record unique.
From this sample, fractions are taken, the fractions are analyzed and the analysis gives results (so all 1 to many relations)
if i continue using keys the way i use them in the sample table example i will end up in the results table with the following fields identifying a record:
InstituteID
SampleID
FractionMethodID
AnalysisMethodID
MeaurementNumber
CASNumber (identifying a result)
So the 2 questions i have:
Should i replace this key (build mostly from natural keys) with 1 numeric key value (composite key)? This means more joins to retrieve certain information (all tables have to be included to link results to an institute)? What will work faster?
Or should i replace the natural keys in every table with a composite key and use this composite keys as replacement for natural keys but use them in the same way i use the natural keys now so i am still able to use less joins to retrieve data? (you know what i mean here?)|||It is a matter of choice and style. I make sure every table I create has a non-composite primary key, usually surrogate.
Regardless, you will want a unique index/constraint on InstituteID/SampleID if you elect not to make it the primary key.|||I have read pages and pages about this topic already, but every person, site or newsgroup seems to have a different opinion. That makes it not easier to come to a final decission. Even checking performance afterwards seems no option since at this moment we have no idea about how big the database will grow and this also seems to influences what path to take.
It all comes down to the following 1 question:
What works faster for data RETRIEVAL. Normalize as far as u can go and generate surrogate keys in every table where necessary, resulting in more joins to retrieve data or use natural composite keys that can result in keys up to 7 fields BUT not having to join a lot of tables to get the same result? Expect the database to grow pretty large over time.
If anyone could answer that it would help greatly in coming to a decission about what path to take.|||I think you are trying to make it simpler than it is. You really can't boil it down to that one either-or decision.
Database design is what it is because thought and trial-n-error have brought it to where it is. Complete/Full normalization is ideal if you want to get anything from anywhere, coming from any direction. The reality is that there are/may be tradeoffs to performance in a fully-normalized DB. This accounts for much of the reason there are different LEVELS of normalization.
If you really want speed in the retrieval of data, you perhaps should use an indexed flat file, or a single table with a single unique key. Yeah, going way the other way, but you get the idea.
There is no silver bullet.
Everything's a tradeoff.
Which accounts for the number and varied opinions on the subject.|||I know this is not an easy topic with one streight forward answer. But my problem is that because i am still a rookie with database design i am trying to find out what the things are that are most difficult to do for a DBMS (MS SQL server in this case), before trying to decide with what option i will go (mostly surrogate or natural keys)
I dont expect a clear answer but am hoping that someone who does have experience with the MS SQL server DBMS and who has build a similar database like i do (a database with say six 1 to many related tables and some lookup tables attached to it) could come up with the basic advantages and disadvantages of using surrogate keys in a database similar to mine.
If i have 6 tables and the 6th table uses the primary key of the 5th table + 1 field to make tuples in in this 6th table unique i just have the feeling it is not right since you have a lot of fields in more than 1 table (not properly normalized if u ask me). It feels better to just create 1 surrogate key for each 1 of these fields (which does result in having one surrogate primary key unqiue for a tuple and having a surrogate reference key refering to its parent table.)
But am i right here? Will Select queries run fast in both systems as long as the tables are properly indexed or does 1 of these methods have a sgnificant disadvantage opposed to the other?
I just want to get informed well before i am making a decission based on a feeling that is not right :)|||If the database is expected to be large, you do not want to carry around any extra storage space. Bigger backups, more resources to archive and purge data etc.
It's not a question of all or nothing, it's possible to use natural keys to a certain level in the hierarchy, then at some point assign a surrogate identity for further referencing from that point.
Personally I prefer to use natural keys, but use surrogates when it makes sense to do so.
As for the performance questions you had, I think neither method has a distinct advantage over the other. That depends more on usage patterns of the data (and indexes). But methodwise, properly implemented both are ok.
rockmoose|||So when does it make sense for you to use surrogates?|||After reading a lot i am leaning toward surrogate keys now. Mostly because using a number of colums as primary key and clustering them makes the the indexes larger, which can result in deecreased performance. Next to that a lot more storage is required and updates to data (that won't hapen often though) require a lot of cascade updating.
The only big disadvantage is having to use most tables in every query to filter on certain data. However i have the feeling that when the DBMS is smart enough in query optimization this shoulden't cause a too high performance penalty|||From a performance and technical perspective an ideal key is thin, stable and monotonically increasing. From a usage perspective it also represent a business meaning.
thin - this is particularily important if the key is going to be propagated to large child tables, you don't want to carry the overhead of large (or multiple) column keys.
stable - a changing key is not a good choice of key, changes will have to be propagated throughout the database.
monotonicaly increasing - this is purely for technical and perf reasons, for large tables with many inserts this is important. Not so important for "static" or small tables.
convey business meaning - easier to work with, less lookups/joins necessary.
if a table has surrogate key, you still need a unique constraint to make sure that no duplicate data is inserted.
So when does it make sense to me?
Well, when a surrogate would clearly give more of the above benefits than a natural key would.
When I design a database and introduce a surrogate, I want to be able to justify it.
e.g. "I put a surrogate here, because the natural key is 4 columns and 32 bytes wide, and it is going to be propagated into this 20 mil row transaction table"
Did you read all the links to discussions oj gave you ;) ?!??!
__________________________________
I wish we had a NATURAL JOIN operator
rockmoose|||After reading a lot i am leaning toward surrogate keys now. Mostly because using a number of colums as primary key and clustering them makes the the indexes larger, which can result in deecreased performance. Next to that a lot more storage is required and updates to data (that won't hapen often though) require a lot of cascade updating.
The only big disadvantage is having to use most tables in every query to filter on certain data. However i have the feeling that when the DBMS is smart enough in query optimization this shoulden't cause a too high performance penalty
You can create views that do the joins for you, and query these.
Have a physical storage layer with surrogates, and a logical view layer with more "business meaning".
IMO, when introducing a surrogate key one is introducing an extra layer of indirection. i.e. we tell the system that this surrogate stands for this natural key. And that from this point we have to perform a lookup to access the data.
This should be done judiciously and for a reason.|||Yes i did read those topics, but for a beginning db developper it still makes it not easy to decide what option to choose.
Even if i go for surrogate keys you have the option to propage the key into the child tables (again resulting in multiple column keys but at least limiting the size) or don't do this and generate a new surrogate key in each table where no keys are propagated.
About this last i have read nowhere in any of the topics on the google news groups
Is there even a sugestion for when to go for surrogates? I mean if a key consist of more than X colums or the column length of a key is larger than y characters or bytes (If the key is stable and unique?).
Another thing that might be good to know for me is if a key field is say 20 characters what influence will that have on the index generated on such field? Will it be larger than an index generated on a 5 character field?
Subscribe to:
Posts (Atom)