April 10, 2008 at 1:22 pm
My boss keeps preaching the following design principles:
1. No identity columns.
2. Always start from a 3rd normal form design then denormalize if needed.
3. No direct access to the physical tables. Always use views and let the db engine take care of things like CRUD.
Other than #2, I don't really agree or understand these points and I would like to get your thoughts on them.
Anybody?
April 10, 2008 at 1:35 pm
My opinions on the subject:
1. I use identity, I consider it a surrogate key (I know Celko and others disagree) and use it mainly for relationships within the database. I also identify and define the truly unique key on the table whenever possible.
2. I agree that starting at 3rd or even 4th normal form and denormalizing as needed. It really depends on your application. Lots of CRUD operations or mostly reporting. The more reporting the more I denormalize.
3. I try to not grant any direct table access, even for select. I normally use stored procedures for all data access and modification. It gives me, as the DBA, more control over access (granting select now allows queries written in Access or Excel) and means I can more easily identify and tune queries.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 10, 2008 at 2:24 pm
Thanks for your input.
For #1, I guess I didn't make it clear. I meant to ask that whether you should use the "Identity" data type for the primary key field or you should have a separate table which stores the sequential number generated from some application logic?
For #3, I was trying to find out if it makes sense to disallow *any* kind of operations on physical tables from *any* forms of SQL statements, regardless of stored procedures or not. My boss's view was that such operations should only be done on views, not physical tables.
April 10, 2008 at 2:32 pm
For 1: That is essentially what the Identity function does. The only caveat is that a failed insert will increment Identity while using your own sequence table allows you to control it. There may also be business reasons for generating your own key field, but if you are generating a sequential number, shy not let the server handle it for you?
For 3: While you can do CRUD operations against a view, what are you really updating? The base table(s), so you are really just trying to hide the base tables from the users and a stored procedure does that as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 11, 2008 at 5:50 am
I have to agree. 2 is fine. 1 & 3 are silly limitations. If you're going with pure natural keys, maybe then you never use identity, but if you're just going to use some other method of generating identity, then you're doing work that the system could do for you. Silly.
As to letting the "dbengine handle crud" by only accessing data through views.... Sorry, that's crud. Someone is going to have to write an INSERT statement. The "dbengine" doesn't create them for you. Most instances of masking off data access is done by limiting that access to stored procedures. You could look at using some kind of ORM tool or LINQ (we're going through this mess right now where I work), but then, again, an external process is handling the creation of INSERT statements (as well as everything else).
Maybe it's just miscommunication?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2008 at 6:18 am
Grant,
Why don't you tell us how you really feel about ORM and Linq to SQL? You might want to check out Andy Warren's blog as he has several posts about Linq to SQL from his perspective as a DBA.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 11, 2008 at 6:26 am
The problem is, I'd be telling you how I feel. I have yet to work on a major project using ORM so I can't give you a list of quantifiable issues. As a matter of fact, in preparation for working on an ORM project, I'm trying to get a list of quantifiable issues. I will give you my current list of concerns:
• I/O increase due to "chattier" applications
• I/O increase due to loading larger data sets more frequently
? Ensure the use of "lazy" collections
• General performance issues due to "generic" procedures using less efficient access methods
? This can also be a "every problem is a nail" type of thing. See: http://adamesterline.com/2007/07/24/nhibernate-performance-lesson-relearned/
• Zero possibility to tune queries in a production setting
• Reporting
? This includes transactional level reporting as well as moving data between a model driven design and a more flexible design (normalized or star schema or whatever) that better supports reporting. Coding time reduced on the front end is, to some degree, tacked on to the back-end.
? A data cleansing mechanism may be required.
• Data integrity
? Generated structures are dependent on discovery to determine the proper constraints required on the data, or all constraints are assumed to be in the code.
? Without integrity maintained with data the possibility of "dirty" data is increased ("USA", "U.S.A.","US" all values entered through app).
As far as LINQ goes, we're probably not going down that road in-house. It looks like nHibernate is the current leader. I don't think LINQ as a general concept is a bad idea. I'm just not too crazy about LINQ to SQL as currently configured and documented.
How's that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2008 at 6:52 am
Grant,
All good points and mostly ones I have heard and made before. Having been mostly a Hybrid SQL DBA\Developer in small shops I have not encountered nHibernate, but I have looked at SubSonic and now Linq to SQL. I am trying to keep an open mind about the products, but performance and security concern me with any product like this. I come from the stored procedures camp and, at least with Linq to SQL, there is not an easy way to use sp's for selects, you can fairly simply use them for CRUD operations. It's always interesting to hear what others think too.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply