|
Part Two - Implementing the Data Model
In the last article, we learned about the reasons for and against a normalized data model. We discussed
he basic design of a normalized model versus a non-normalized model. We talked in abstract terms and in
generalizations. In this article, we'll get into the meat of the model - we'll discuss real-world
implementation specifics and the actual table layouts.
Review
In article one, we discussed the pros for using a user defined data model - we give your customer the power
to manage their own attributes, beyond the application and without the need for costly development upgrades.
For simple changes, this works well. We discussed an example where we needed a new attribute for a chair
that wasn't in the initial design criteria. If we used a static data model, the implications are that you
now need to change your data model which typically translates into an application change.
Using the proposed model, these changes can be minimized significantly.
The Data Model Specifics
What we now can discuss is how, specifically, we can define our application and data model together such that
we can accommodate these changes. In our previous article, we discussed things in abstract terms, here we will
develop an specific implementation.
Expanding on our chair theme from before, let's say we have a need to define a series of furniture for your
customer, an office management company. Their need is to define a series of offices for their customer base,
allowing for different downstream customer requirements and they want to allow their end users to see their
own inventory.
We would begin by defining a series of large 'objects' in your customers' world - here we have
chairs, desks and plants in a simplified world.
We define a standard table structure for each of these items:
-
Unique Identifier column
Creation time stamp column
Creator identification column
Modification time stamp column
Modifier identification column
- Each has an attribute table
-
Unique Attribute Identifier column
Relation back to master table
Attribute type identifier
Attribute value
Creation time stamp column
Creator identification column
Modification time stamp column
Modifier identification column
The tables themselves would look like this:
The SQL would be:
create table chair (
chair_id bigint not null identity (1,1),
created_on smalldatetime,
created_by bigint,
modified_on smalldatetime,
modified_by bigint)
alter table chair add
constraint pk_chair primary key clustered
(
chair_id
)
create table chair_attribute (
chair_attribute_id bigint not null identity (1,1),
chair_id bigint not null,
system_attribute_id bigint not null,
chair_attribute_value,
created_on smalldatetime,
created_by bigint,
modified_on smalldatetime,
modified_by bigint)
alter table chair_attribute add
constraint pk_chair_attribute primary key clustered
(
chair_attribute_id
)
We also need to define our meta-data table. I call this the
system_attribute table. This table holds all of the attribute definitions for the whole database and also defines several criteria that are used to ensure data integrity.
The system_attribute table is also broken into classes by the system_attribute_class table.
These tables would look like this:
The system_attribute_class table allows us to easily break up the various groups of attributes in the database - chairs versus plants in our example. This simply makes it easier to find the attributes in our table when we want to do a search.
The system_attribute table is the meat of the whole system, however. This table is what drives our user interface, our data model and ultimately the application code.
Looking at the column names, we have the following:
SYSTEM_ATTRIBUTE_ID
This is our identity column and is system generated. This will be the reference in our attribute tables (chair_attribute, desk_attribute, etc) to tell the application what the row refers to.
SYSTEM_ATTRIBUTE_CLASS_ID
This refers back to the system_attribute_class table, allowing us to segment the rows easily.
SYSTEM_ATTRIBUTE_NAME
This is the human readable name of our attribute. I typically use these as a look up into the table, since it is possible - although not suggested - that the id column may change in different implementations. Names like chair_color or desk_style would be examples of this column.
SYSTEM_ATTRIBUTE_DESCRIPTION
I use a description column in the table so that when you return to the application six months later you can easily see what it is you're looking at. When the system_attribute_name is chair_color it is pretty easy to remember what you were thinking at the time, but chair_nbr_leg might be a little confusing. I don't typically display this out to the application, but it could be used as a tool tip in some cases.
SYSTEM_ATTRIBUTE_DISPLAY_TEXT
This is the label for the user interface. Setting it here allows you to pull the text from the database nice and easily - you can localize easily and make UI changes without having to resort to changing the code.
SYSTEM_ATTRIBUTE_DISPLAY_ORDER
This is a sequential number which I use to drive the display of the items - zero to however many attributes you have for an item. When you return the results, sort by this and your UI will display in the order you want them to.
SYSTEM_ATTRIBUTE_DEFAULT
This allows us to define some default value for our item - we can leave it blank or we can populate it and if the application wishes, it can assume this default by passing a null value when the attribute is inserted.
SYSTEM_ATTRIBUTE_VERIFY_SQL
Use this to call a stored procedure to return a result back to your application telling it if the insert, edit or delete can take place. You can do bounds checking, verify foreign keys or other items. Putting it here allows you to drive the functionality into your application or into the stored procedure.
SYSTEM_ATTRIBUTE_LOOKUP_SQL
This is a powerful feature. Want to have the item be a dropdown combo? Set up a lookup stored procedure here and have the combo items populated directly. Further, we can build into our insert and update stored procedures the logic to verify that the attributes are within the list of this lookup.
T-SQL to the Rescue
Now we need an easy method of accessing our data. We'll do that by defining a standard set up stored procedures for us to use when accessing, deleting, editing or inserting records.
We'll create stored procedures with the following naming convention:
<table>_get
<table>_insert
<table>_update
<table>_delete
Therefore, we will get the following stored procedures:
Chair_get; chair_insert; chair_update; chair_delete
Chair_attribute_get; chair_attribute_insert; chair_attribute_update; chair_attribute_delete
The SQL syntax for these would be:
create procedure chair_get
@chair_id bigint
as
select * from chair where chair_id = @chair_id
create procedure chair_insert
@created_by bigint,
@chair_id bigint output
as
insert into chair (created_by, created_on)
values (@created_by, getdate())
if @@ERROR <> 0
begin
-- not good, we need to reset the
-- chair id and return the error
@chair_id = 0
return @@ERROR
end
else
begin
@chair_id = @@IDENTITY
return 0
end
create procedure chair_update
@chair_id bigint,
@modified_by bigint
as
-- update the chair table, we only need
-- to touch the modified columns since
-- that's the only real data here
update chair
set
modified_by = @modified_by,
modified_on = getdate()
where chair_id = @chair_id
if @@ERROR = 0
return 0
else
return @@ERROR
You can also modify this to handle an audit trail. As you'll see in the attribute functions, we call this whenever we make an attribute change and we could create a simple audit trail to track who makes the changes and when. For now, we can leave this as a simple 'last modified' tracker.
create procedure chair_delete
@chair_id bigint
as
-- delete the attributes before we move ahead
delete chair_attribute where chair_id = @chair_id
if @@ERROR <> 0
return @@ERROR
-- OK, we can proceed
delete chair where chair_id = @chair_id
return @@ERROR
The attribute stored procedures are a little more complicated - they will need to refer back to the system_attribute table and to the main item tables.
create procedure chair_attribute_get
@chair_id bigint
-- said is short for system_attribute_id
@chair_said bigint = 0
as
-- get all the attributes for this chair
if @chair_said = 0
select
chair_id,
chair.modified_on chair_modified_on,
chair.modified_by chair_modified_by,
chair.created_on chair_created_on,
chair.created_by chair_created_by,
chair_attribute_value,
system_attribute_name,
system_attribute_description,
system_attribute_display_text,
system_attribute_display_order,
ca.modified_on chair_attribute_modified_on,
ca.modified_by chair_attribute_modified_by,
ca.created_on chair_attribute_created_on,
ca.created_by chair_attribute_created_by,
ca.chair_attribute_id
from chair
left join chair_attribute ca on
ca.chair_id = chair.chair_id
left join system_attribute sa on
sa.system_attribute_id =
ca.system_attribute_id
where chair.chair_id = @chair_id
else -- we want a particular said only
select
chair_id,
chair.modified_on chair_modified_on,
chair.modified_by chair_modified_by,
chair.created_on chair_created_on,
chair.created_by chair_created_by,
chair_attribute_value,
system_attribute_name,
system_attribute_description,
system_attribute_display_text,
system_attribute_display_order,
ca.modified_on chair_attribute_modified_on,
ca.modified_by chair_attribute_modified_by,
ca.created_on chair_attribute_created_on,
ca.created_by chair_attribute_created_by,
ca.chair_attribute_id
from chair
left join chair_attribute ca on
ca.chair_id = chair.chair_id
left join system_attribute sa on
sa.system_attribute_id =
ca.system_attribute_id
where
chair.chair_id = @chair_id and
ca.system_option_id = @chair_said
This stored procedure gives us either a list of all of the attributes for a chair, or we can optionally request a specific attribute from the database.
create procedure chair_attribute_insert
@chair_id bigint,
@said bigint,
@chair_attribute_value varchar(8000),
@created_by bigint,
@chair_attribute_id bigint output,
@error_text varchar(500) output
as
/* first we need to test that the
passed value is legal */
-- is there a verify for this said?
declare @sp_name varchar(255),
@sp_text varchar(500),
@verify_status
select @sp_name = system_attribute_verify_sql
from system_attribute
where system_attribute_id = @said
/*
if sp_name is filled, then we have something to test
all of the verify stored procedures take one value
and return a 0 or a 1. 0 means the value is OK,
1 means the value failed.
The procedures take 2 parameters, the value in and
an output parameter with the error text.
*/
if @sp_name <> '' begin
exec @verify_status = @sp_name
@chair_attribute_value, @sp_text output
if @verify_status = 1 begin
-- we have a failure in the verify process
set @error_text = @sp_text
return 1
end
-- we were either successful in the verify
-- or didn't need to
insert into chair_attribute
(chair_id, chair_attribute_value, system_attribute_id,
created_by, created_on)
values
(@chair_id, @chair_attribute_value, @said,
@created_by, getdate())
if @@ERROR = 0
set @chair_attribute_id = @@IDENTITY
-- mark the chair itself as modified
exec chair_update @chair_id, @created_by
end -- end verify_status = 0
As you can see, this stored procedure gets much more complex. We now have to worry about getting the verification procedure, if it exists, from the database and run that. If that is successful, then we can proceed on with the insert process.
If it isn't, we need to record the text from the procedure and pass that back to our application as an intelligent response back to the user. Things like: "Error out of range. Only a number between 1 and 10 is allowed" would be an example of the returned string. The application can then use this and display it to the user so that they can fix their values.
We also need to touch the parent item (the chair in this case) and set the modified information.
Finally, we exit and return 0 back to the calling application.
create procedure chair_attribute_update
@chair_attribute_id bigint,
@chair_id bigint,
@said bigint,
@modified_by bigint,
@error_text
as
/* We need to verify the value first */
declare @sp_name varchar(255),
@sp_text varchar(500),
@verify_status
select @sp_name = system_attribute_verify_sql
from system_attribute
where system_attribute_id = @said
/*
if sp_name is filled, then we have something to
test all of the verify stored procedures take
one value and return a 0 or a 1. 0 means the
value is OK, 1 means the value failed.
The procedures take 2 parameters, the value in
and an output parameter with the error text.
*/
if @sp_name <> '' begin
exec @verify_status = @sp_name
@chair_attribute_value, @sp_text output
if @verify_status = 1 begin
-- we have a failure in the verify process
set @error_text = @sp_text
return 1
end
/* if we made it this far, we're OK */
update chair_attribute
set
chair_attribute_value = @chair_attribute_value,
chair_id = @chair_id,
system_attribute_id = @said,
modified_by = @modified_by,
modified_on = @modified_on
where
chair_attribute_id = @chair_attribute_id
/* we need to touch the parent */
exec chair_update @chair_id, @modified_by
-- we can simply return the @@ERROR here
return @@ERROR
This is very similar to the insert procedure. We need to ensure the passed values are legal and that then we can update the item. We need to pass in the id of the current attribute item and then we can change any and all of the items on the record.
Again, we need to touch the parent item (the chair) so that we track the modified time and user.
create procedure chair_attribute_delete
@chair_attribute_id bigint,
@modified_by bigint
as
/* we need to get the parent item before we continue */
declare @chair_id bigint
select @chair_id = chair_id from chair_attribute
where chair_attribute_id = @chair_attribute_id
delete chair_attribute
where chair_attribute_id = @chair_attribute_id
if @@ERROR = 0
exec chair_update @chair_id, @modified_by
return @@ERROR
The delete procedure first determines the current parent item of the record we want to delete, then it deletes the record and touches the parent.
What's Next?
In the next article, we'll dig into the application side of the model. We'll discuss building the user interface and how to take advantage of the Visual Studio tools to quickly and easily access the data.
We'll also get into the verification stored procedures and how those are set up and how they can be used.
About the Author
Peter Scheffler is an independent consultant from Ontario Canada. He specializes in developing back office solutions around business critical database applications. He has developed applications using many different database technologies (SQL/Server, Oracle, mySQL and Access) and enabling technologies (ASP/ASP.NET, VB/VB.NET, C/C++, php and Java.)
Prior to being a full-time independent consultant, Peter spent more than 10 years in the enterprise software development community as a developer, pre-sales/post-sales consultant and in various product management positions.
When he's not busy hacking at his keyboard, he's busy at the hockey rink with his kids or camping in his house-on-wheels. You can read about his misadventures in his Camping Blog at
http://www.scheffler-consulting.com.
Peter can be reached at peter@scheffler-consulting.com.
|
