While not a CF guy and being more of a DB guy, I do develop applications
in .Net. The database to me is always first. Without a good foundation,
you will not have a good building (application).
Normalizing databases is optimizing them. The simplest rule to use for
normalizing them is to ask one question faithfully and answer it faithfully.
Can there ever be more than one? If you do that completely truthfully,
you can take the database to fifth normal form, and easily to third normal.
The LOGICAL design of a database can and will, 99% of the time, be different
from the PHYSICAL design. The logical design can be equated with the
objects you are talking about. Take the following example...
Logical Design
Employee Entity
Employee ID
First Name
Middle Name
Last name
Name Prefix (Salutation)
Name Suffix
Mailing Address
Physical Address
Department
Supervisor
Job Title
Pay rate
ETC...
Physical Entities
Employee EmpAddress EmpDepts EmpPay
EmployeeID EmployeeID EmployeeID EmployeeID
FirstName StreetAddress DepartmentID EffectiveDate
MiddleName CityName EffectiveDate PayGrade
LastName StateCode DepartmentHeadID PayRate
PostalCode PayType
AddressTypeID
By encapsulating the physical design into an object based model,
you can get all of the information about an employee with out
having to know the physical design of the database. You simply
use the object structure for it. In .Net, it is the new Entity
Framework or using a tool like Codesmith with netTiers.
Since the calls to the DB direct from CF or any other front end for
that matter, are compiled on the fly and optimized on the fly, it
is better to do stored procedures.
It is a shame that the developers in the instances some one gave
did not know how to manage their stored procedures. Here, we are
moving completely from a dynamic sql environment to stored procedures
only. We are using the table name with a p as the prefix. We use
modes in them and have multiple modes/selects within the stored
procedure. When we hit a certain number of lines, we start a second
SP with a 2 on the end, and keep it going that way. We also comment
the code in the SP as to where it is being used. I have some places
that the same SP mode is being used 30 or more times in different
portions of the CF code.
Oh, and the CF developers love it because they do not have to understand
the backend database. They just use the SPs and move on to coding the GUI.
Robert
At 02:23 AM 12/15/2008, you wrote:
>Date: Sun, Dec 14 2008 8:55 am
>From: Shane Heasley
>
>
>To me, the rules for normalizing databases are just a roundabout way
>of saying - build them as objects.
>
>You put rationally grouped data together in tables in such a way that
>you avoid nulls and do not duplicate data anywhere.
>
>First Normal
>Eliminate duplicate columns
>Group related data and identify each row with a primary key
>
>Second Normal
>Remove subsets of data that are duplicated and put them in a separate
>table (create a related ojbect)
>Create relationships through the use of foreign keys
>
>Third Normal
>Remove columns that are not dependent on the primary key. (This one
>can be a little iffy sometimes but basically you could restate it as -
>make sure your object only has related items).
>
>If you follow these rules don't you end up with objects? In the broad
>sense of the word - employee objects, address objects etc.
>
>To me you should architect the system and then build the database
>first. From the business point of view the database is the, by far,
>most important element. It is where the business "treasure" is -
>where the value is. The middleware or the GUI can always be changed
>(and probably will be) but the data will only migrate and if it is in
>SQL Server or Oracle probably not even that. Also, if the database is
>well written then the middleware becomes easy to write, your SQL
>statements tend to be very simple etc.
>
>I write like you Aaron - database first.
Robert Stewart
ProjecTools.com
713-371-9840 X1305
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the "Houston ColdFusion
Users' Group" discussion list.
To unsubscribe, send email to [email protected]
For more options, visit http://groups.google.com/group/houcfug?hl=en
-~----------~----~----~----~------~----~------~--~---
