March 23, 2010 at 7:28 pm
It is often said the design is the most important in the DB's long term success, and is the foundation on which the app will sit.
What all does this imply? I am sure the good logical design (Normalization) is very critical. Can you think of any thing else specifically, during that stage of the development?
Dan
March 24, 2010 at 1:55 am
Hi
Well think about the following scenario:
You designed a database according the requests and leave some requests for the future releases. You designed a data and business layer according your busniess needs. After the first release you starting to implement the open requests and figure out that you have to redesign the database tables, because the entities are wrong build. So you normalize your db again. This change has a big impact to you data and business layer components or objects.
Everything you have to change after the implementation costs money, time and nervs.
The database is the basic module of the application. If something has to be change on the db or the db is wrong design, the whole application must redesigend.
March 24, 2010 at 4:04 am
Normalize, normalize, and then normalize again. This is the key to a successful db design.
What else belongs to the design phase? These points come to my mind:
Proper data types
Constraints
Primary Keys
Foreign keys
CHECKs
Unique constraints
Nullable columns only for nullable attributes
I think all the rest belongs to the development phase, but, to be successful, needs to ba based on a good design.
Hope this helps
Gianluca
-- Gianluca Sartori
March 24, 2010 at 11:35 am
I would echo everything Gianluca said -- and add to that a) decisions on your security model (who gets access to what), and b) how you are going to do your CRUD work -- whether requiring it all to be done via stored procs, or allowing other code (as in ADO.NET) to run DML queries directly.
Rob Schripsema
Propack, Inc.
March 24, 2010 at 11:40 am
For CRUD, the reason only SP is advised is because of SQL Injection Attack possibility?
are there any good times/reasons to allow DML from App code?
March 24, 2010 at 11:43 am
repent_kog_is_near (3/23/2010)
I am sure the good logical design (Normalization) is very critical.
Even when normalization to -at least- the 3NF is critical for an OLTP system allow me to point that an optimal design not always includes such data normalization - think on a DWH environment with a star schema scenario 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 24, 2010 at 12:03 pm
PaulB-TheOneAndOnly (3/24/2010)
repent_kog_is_near (3/23/2010)
I am sure the good logical design (Normalization) is very critical.Even when normalization to -at least- the 3NF is critical for an OLTP system allow me to point that an optimal design not always includes such data normalization - think on a DWH environment with a star schema scenario 😉
I assumed the question was about an OLTP system, but my assumptions could have been wrong. That's what happens when you live only on one side of the barricade... 🙂
I don't remember who said that, but I believe it's a gem of wisdom: "Normalize until it hurts, denormalize until it works".
-- Gianluca Sartori
March 24, 2010 at 12:05 pm
3NF+ for OLTP and 0 NF for OLAP? 😎
March 24, 2010 at 12:06 pm
repent_kog_is_near (3/24/2010)
For CRUD, the reason only SP is advised is because of SQL Injection Attack possibility?are there any good times/reasons to allow DML from App code?
Using stored procedures CAN help with preventing SQL injection attacks, but those can also be avoided using parameterized queries in ADO.NET as well. A more common reason is to standardize all entry/retrieval to a database -- so that ANY application wishing to use or modify data in a database has to go in through the portals created and managed by the DBA -- keeping precious data out of the hands of those rogue developers. Sometimes a SP will help with incorporating some more complex validation or business rules.
In my real-world experience, the decisition to use (or not) SP's for the CRUD work often depends on the makeup of your dev team -- are the strengths/interests in app coding (C#/VB or whatever) or in T-SQL development. Also, just how particular is the DBA about the purity of the data? Often, just for the sake of expediency, much or most of the DML work is done in dynamic queries generated in code (usually, "just to prove that it works -- we'll change it over to SP's later") that never goes away once things go to production. From a purist point of view, this is not good. However, the product does have to get into production somehow, someday, perfect or not....
The most common place to allow dynamic queries to creep in in code is when you have some complex search function you have to implement, with optional parameters, multi-valued parameters, or other complexities. The work of building a SP that will accommodate those types of queries is often so daunting, and relatively easy in C#/VB or whatever, that we just let that slide into the app code. Again, not the ideal, but one has to be realistic....
Rob Schripsema
Propack, Inc.
March 24, 2010 at 12:10 pm
good perspective, Rob.
March 24, 2010 at 12:10 pm
repent_kog_is_near (3/24/2010)
are there any good times/reasons to allow DML from App code?
Generally speaking, DML from app is about dynamic SQL and/or ORM middleware. I don't see any other good reason to allow it, nor I think those fall into the "good reasons".:-P
-- Gianluca Sartori
March 24, 2010 at 12:12 pm
Rob Schripsema (3/24/2010)
The most common place to allow dynamic queries to creep in in code is when you have some complex search function you have to implement, with optional parameters, multi-valued parameters, or other complexities. The work of building a SP that will accommodate those types of queries is often so daunting, and relatively easy in C#/VB or whatever, that we just let that slide into the app code. Again, not the ideal, but one has to be realistic....
I agree 100% this is the most common backdoor for dynamic SQL, I don't agree it's difficult to implement in stored procedures. Optional joins / subqueries in sql text depending on parameters is easy to implement and with sp_executesql you can pass in all parameters, regardless of wether they're used or not.
-- Gianluca Sartori
March 24, 2010 at 12:25 pm
I think most problems with database applications are built-in at the design stage.
Once you do a poor job of modeling data, it becomes almost impossible to make a significant change to the database design without impacting a huge amount of application code. What usually follows a poor design is a series of increasingly shaky hacks to work around the poor design at higher and higher cost.
Unfortunately, many application designers do not realize this and are anxious to get to the coding stage of development, so they throw together a poor databases design that causes a huge increase in costs a later stages.
The time spent gaining a good understanding of the business process and modeling the data pays off in faster development and lower maintenance costs.
March 24, 2010 at 1:26 pm
repent_kog_is_near (3/24/2010)
3NF+ for OLTP and 0 NF for OLAP?
I wouldn't go that far.
I'm in agreement about 3NF+ for OLTP - actually I do believe there is nothing you can't solve with 3NF for OLTP purposes.
On the other side my personal preference is Kimball's star-schema where you build datamarts with a central FACTual table surrounded by DIMensional tables. Looking further somebody might say that FACT tables are of the 3NF while DIM tables are of the 2NF.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 24, 2010 at 8:51 pm
Paul,
Do you have a link for Kimball's star-schema that you refer to, for OLAPs?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply