Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Data belongs in your tables -- not in your code

Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables.

 

Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.

 

Example #1

 

Your company defines 3 shifts per day:  Shift 1 is 12:00AM-8AM, Shift 2 is 8AM-4PM, Shift 3 is 4PM-12AM.

 

So, when you need to evaluate a DateTime variable to see which shift it falls into, you write:

 

SELECT ..., CASE WHEN Hour(SomeDate) < 8 THEN 1

            WHEN Hour(SomeDate) < 16 THEN 2

            ELSE 3

       END as ShiftNo

FROM ...

 

Great, except you now have data stored in your code -- not in a table!  Let's store our data where it belongs by creating a “Shifts” table:

 

Shifts

ShiftNo (PK)

StartHour

EndHour

Description

1

0

7

Morning

2

8

15

Day

3

16

23

Night


And now you simply write:

 

SELECT ...., Shift.ShiftNo, Shift.Description

FROM ...

INNER JOIN Shift
  ON Hour(SomeDate) BETWEEN Shift.StartHour and Shift.EndHour

 

This allows you to add descriptions, shift managers, calculation adjustments, anything you need that is related to a shift.  In addition, you can use this Shift table to drive reports and now changing to a 4 shift day is a matter of changing data and not by changing code.  Even better, add a "WeekEnd?" flag or "WeekDay" column to your shift table to allow for different shifts for different days of the week; add more columns for more variations.  Even historical changes can be represented (and clearly documented) in this table by adding StartDate/EndDate columns to the primary key.

 

(Note: this is a simplified example, of course, since a Shift will often last past midnight, but you get the basic idea.)

 

Example #2

 

Your company pays out bonuses based on the employee type: Managers get 10% of their annual salary, Regular employees get 7%, and Admins get 5%.  So, when it is time to calculate the bonus, you simply write:

 

SELECT ..., Salary * CASE EmpType WHEN 'Manager' THEN .10 
                                  WHEN 'Regular' THEN .07
                                  WHEN 'Admin' THEN .05 
                                  ELSE 0
                     END as Bonus

FROM ...

 

In addition, on your personnel reports, you always want to list Managers first, followed by regular employees, followed by Admins; you cannot sort alphabetically, so you write:

 

SELECT ...

FROM ...

ORDER BY CASE EmpType WHEN 'Manager' THEN 1
                      WHEN 'Regular' THEN 2 
                      WHEN 'Admin' THEN 3
                      ELSE 4
         END ASC

 

Works great! But you do have an EmployeeType table, right?  If you don't, then it's time to create it.  And even if you already have it, let's consider putting our data in the table, not in the code:


EmployeeTypes

EmpType (PK)

BonusPct

Sort

'Manager'

.10

1

'Regular'

.07

2

'Admin'

.05

3


Now, a simple join to that table gives you what you need.  No more CASE's, no more things hard-coded in your SELECT.  In addition, now changing those percentages or creating new Employee Types involving editing the contents of tables, as opposed to editing your code.  It’s pretty simple and obvious, right?   Not always!

 

Example #3

 

At your company, certain ProductTypes should not show up on the Annual reports, since they are internal only, have been phased out, or were used for testing purposes.   We’ve all had to exclude certain values from reports, and it is easy enough to do, right?

 

SELECT ..

FROM ...

WHERE ProductTypeID Not IN (24,203,1,293)

 

It’s simple, and it works great!  But I think you know what is coming -- why are we storing this data in our code, when it should be in our product types table?  Add a bit column or some other indicator to the table, and flag those items in your data so you can remove these lists of values from your code.  Even if you cannot edit the ProductTypes table, you might consider this creating a new table:


ProductTypeExclusions

ProductTypeID (PK, FK)

Notes

24

'Testing'

203

'Converted to product type #12'

1

'Internal'

293

‘Testing’


With that, a simple LEFT OUTER JOIN to this table lets you know when something should be excluded:

 

SELECT ...

FROM ...

LEFT OUTER JOIN ProductTypeExclusions PEX
  ON ...

WHERE PEX.ProductTypeID is NULL

 

Now, we have documentation in our data as to which product types are excluded (and why), we can easily make changes w/o recompiling our code, and we can even write reports using this table.   We might add date ranges to this table or other factors that determine when to exclude or include certain ProductTypes from our reports – the more you put in your table, the less you have to put in your code. 

 

As a nice side effect, our new SELECT statement clearly tell us why we are excluding certain items from the report, and we no longer need to wonder what that big list of ID’s means! 

 

Example #4

 

And, finally, consider “look-up” tables.  We all use them -- it's a great way to provide a list of valid values for certain columns; usually, they don't do much else:

 

StatusCodes

StatusCode (PK)

Description

A

Approved

P

Preliminary

R

Rejected

D

Deleted

H

Archived

 

Raise your hand if you have a table like this in your system.   But as it is, how much does this table really tell us? What does 'Rejected' really mean?  To find out, we often have to look at our application or T-SQL code.  There, we would write things like:

 

... WHERE StatusCode IN ('A','H') ... return everything that is valid

 

... IF StatusCode NOT IN ('P','R','A') THEN ... it cannot be edited

 

... WHERE StatusCode IN ('P','R') ... show all objects that can be reviewed

 

... IF StatusCode IN ('P','R') THEN ... allow it to be deleted

 

And so on ... (Note that this gets even worse when we use IDENTITIES as the PK for these lookup tables, but that's a topic for another day)  Again, this is a simplistic example and maybe some of those codes don't make sense, but you should get the idea.  

But what are we doing here, really?   What is the point of that Status table?  Are we using it to our full advantage?  Should we really have data (the status code values) hard-coded into our code?  What happens to our code when we add new statuses?

 

Once again, the answer is obvious: try to store more data in your tables, and take it out of your code.  It may not be quickly apparent with this example, but consider this:

  

StatusCodes

StatusCode (PK)

Description

IsValid

IsEditable

IsReviewable

IsDeletable

A

Approved

1

1

0

0

P

Preliminary

0

1

1

1

R

Rejected

0

1

1

1

D

Deleted

0

0

0

0

H

Archived

1

0

0

0

 

Now, all of a sudden, our StatusCodes table has very clear definitions of what those codes actually mean (it is very self-documenting now!), and how our application and reports use them.  If we add columns for all of the different core actions that the app can do in this table, suddenly we find that we almost never need to hard-code any data in our code, but rather it now lives in our tables!  With a simple JOIN to our Status table, we can now write:

 

... WHERE IsValid =1 ...

IF isEditable = 0 THEN ...

... WHERE isReviewable = 1 ...

IF isDeleteable = 1 THEN ...

 

And so on.  And now we can change status codes, create new ones, and reclassify things as needed -- all in our data. 

 

Conclusion

 

On the surface, this is all obvious stuff.  But we all sometimes find ourselves putting more data in our code than we should, sometimes because it is quicker, other times because we can't change our schema, but often because we didn't plan ahead as much as we should have.

 

I hope this has given you some ideas of ways you can put more of your data where it belongs -- in your tables.  You will find your code much shorter and easier to understand, and you will find that suddenly your data really documents your system well and allows for surprisingly major changes to be made in your tables alone.


(Update: more on data versus code here.)


see also:


Print | posted on Friday, February 10, 2006 10:41 AM | Filed Under [ T-SQL Techniques Efficiency Database Design ]

Feedback

Gravatar

# re: Data in Tables -- Not Code

I totally agree.
This way if you need to change something you can do it in 1 place instead of in 5 procs and 3 views
It's also easier to add new roles/percentages, and of course delete stuff
Another benefit of storing data in a table is that you can have start dates and end dates for certain things
2/10/2006 12:26 PM | Denis
Gravatar

# re: Data in Tables -- Not Code

None of this is difficult or expensive (overhead-wise) - the incremental cost is minimal. You do, however, have to know to do it.

Real life example: How many accounting periods does a US company need? 12? One for each month? 13? One for each month and a closing period? Hard code it that way, and you've got a six-month project to change the maximum. A company I worked for had the good forture of having a designer that put the maximum number of accounting periods as data in a table. When the auditors came and demanded a 14th period - for final adjustments, the change was a deta change, and modifying the reports to omit cents so all 14 columns of data would fit. It took (including testing) about 24 hours of effort.
2/10/2006 2:43 PM | David Tremain
Gravatar

# re: Data in Tables -- Not Code

Basic old advice in my opinion.
2/12/2006 9:47 PM | Jon
Gravatar

# re: Data in Tables -- Not Code

Jon -- Try browsing the SQLTeam forums (or any other SQL Server forum) some day to see how often people throw data into their code. And even experienced programmers often do not do the things I showed in Example #4 -- I'm willing to bet that even an old pro like yourself has written has data hard-coded here and there in your T-SQL. It' sometimes hard to avoid, and we all have done it (myself included), but the point of the post is that it pays to step back to see if there is a way to take even simple pieces of information out of your code and put it into your tables, when often it isn't obvious how to do it or that you even can.


2/13/2006 8:45 AM | Jeff
Gravatar

# Is all of your data in the right place?

Jeff Smith of SQLTeam brings us a great blog post about data in tables vs. data in code.I especially...
2/16/2006 11:30 AM | adam machanic :: sql server prog
Gravatar

# Is all of your data in the right place?

Jeff Smith of SQLTeam brings us a great blog post about data in tables vs. data in code.

I especially...
2/16/2006 11:31 AM | adam machanic :: data manipulati
Gravatar

# Adam Machanic - Is all of your data in the right place?

2/22/2006 3:56 PM | Professional Association for SQL
Gravatar

# re: Data in Tables -- Not Code

I think something that is often missed when talking about this topic, especially in relation to #4 above, is that is can be tempting to avoid creating lookup tables because of the performance implications - if you have dozens of lookup tables, and you need to refer to them all the time in your application code outside of other queries, then those lookup tables are going to end up costing you lots and lots of database calls where the data could just have been stored in an enum, array, or other "Fast" programming construct that can be ready in memory.
Then when you start noticing that 50% of your database queries are on these tiny lookup tables that could have been hardcoded, you might be tempted to slip a few into the code - but you need to keep them in your database as well, for reporting and maybe for compatibility wioth other parts of the app. So now you have the data in two places, even worse.

What I'd like is some easy way, in ASP.Net and classic ASP, to store lookup tables ONLY in the database, but ensure that they are always cached in some sort of in-memory programming construct that is easily accessible - so when an application or web application loads, the first time it needs to reference the lookup table, it asks the DB - but afterwards that table is stored locally, and any subsequent requests for one of those lookup values is served locally from the application's memory.

For a web application, the next requirement is some way to drop all the lookup table data cached, and have the values reloaded when they are next requested - so that you can handle configuration updates etc.

Is this something that exists? Surely it makes sense to make this a part of the .Net framework at least, it must be SUCH a common need... no?
3/1/2006 9:51 AM | Tao
Gravatar

# re: Data in Tables -- Not Code

Tao -- thanks for the comments, but there are a few things maybe I didn't communicate too well in the 4th example. The application *never* queries the status tables directly. Not even once. It gets returned with the data via a standard, efficient, SQL JOIN. If I get the customer, I get the statusCode, status Description, valid things I can do based ont hat status, etc -- all at once. The cost is negligable. (If we worry about the "cost" of performing simple joins in our RDBMS, then we have bigger problems to worry about!) Now the app has all the information it needs to present the UI, validate and present input and actions, and so on, and doesn't need to go query the database again until it actually changes data.

It would be a bad idea, as you mention, to constantly lookup values in these tables every time an action is performed at the client. The idea is that when you get the data, you get ALL the data you need at once and work from that. Like any other query.

I hope this helps clarify things a little.
3/1/2006 11:04 AM | Jeff
Gravatar

# re: Data in Tables -- Not Code

Tao-

You can load this table into a static hashtable if you want.

I have a couple of lookup tables that are used repeatedly in an application. These are used in drop downs and other areas where I am not getting other data. I create a class that has a static hashtable. The first time it is used it is populated with the data from the database. Now I just use a couple of methods that retern the data. I haven't wriiten anything to make it automatically reload because the base data changes so little. If I need to force it to reload the hashtable, I just wait until off hours and do an iisreset. You could of course write an admin page to force it to refresh the hashtable.

I agree with Jeff on this topic. It is easy to get sloppy, but we should be willing to take a step back and look at it. If you can get the extra data in your query that returns data, then great. That means you have one result set with the all of the data in it. If it takes two queries and you are concerned about creating a hot spot in the database, use static data storage in ASP.NET to help out.

3/6/2006 10:40 AM | Chris
Gravatar

# re: Data in Tables -- Not Code

Hi Chris,

That's pretty much what I was getting at - I think in most web aapplications there will be drop-downs, radio buttons, status codes, and other data that should ideally be stored in a lookup table in the DB, but needs to be readily available in-memory for the application. (and that can NOT always be retrieved with other data using joins). The ideal solution, as far as I can see, is storing that data in a hashtable or other collection in the application storage, or other static place. I'm just surprised there is nothing built in to the framework, to make this easier!

Thanks,
Tao
3/17/2006 12:00 PM | Tao
Gravatar

# re: Data in Tables -- Not Code

Caching the data ?
3/17/2006 3:05 PM | Jon
Gravatar

# re: Data in Tables -- Not Code

>>Caching the data ?

Yeah, that's what I would think as well ... for drop-downs and the like, you can put the data in a dataset and cache it. ASP.NET makes this quite easy to do. You can specify a timeout and everything.

3/17/2006 3:13 PM | Jeff
Gravatar

# re: Data in Tables -- Not Code

I'm trying this out, (I've been guilty of putting data in the code and never really thought much of it) BUT the one project i've done to try it out has a new twist. I need to assign values depending upon where a Total falls.

For Example: Sum(PRICE*QTY) to look up commission on a table with a Min and Max column. there are 5 'tiers' that the salesman can fall into and each is identified by his total sales. It's an easy CASE statement with BETWEEN but I'm trying your DATA in a TABLE and can't get the join to work on a SUM.

Any help is always appreciated.

4/20/2006 1:46 PM | Steve
Gravatar

# re: Data in Tables -- Not Code

Steve --

You need to use Derived tables. If you wrap your SELECT and the calculation for SUM(Price*QTY) all in a derived table, then you can simply JOIN that derived table and use that calculation in a join expression.

i.e., simple example:

SELECT S.SalesMan, S.TotalSales, C.Commision
FROM
(SELECT SalesMan, SUM(PRICE*QTY) as TotalSales
FROM Sales
GROUP BY SalesMan) S
INNER JOIN
Commission C
ON
S.TotalSales BETWEEN C.MinSales and C.MaxSales

Derived tables are a very powerful feature and can make your code much shorter, clearer and more readable.

If you have more issues or any other problems, I encourage you to visit the SQLTeam.com forums and post questions there.
4/21/2006 9:40 AM | Jeff
Gravatar

# re: Data belongs in your tables -- not in your code

Also see DBCC PINTABLE for #4
7/23/2007 12:01 PM | Steve H
Gravatar

# re: Data belongs in your tables -- not in your code

As ever, apply good taste. Follow these rules to their absolute conclusion and you'll end up with this:

http://worsethanfailure.com/Articles/The_Enterprise_Rules_Engine.aspx

By all means put your data in tables, but don't get data and business logic confused.
7/23/2007 12:20 PM | Dan
Gravatar

# re: Data belongs in your tables -- not in your code

I'm a beginner at this, so thanks for the post, I have already learned a lot from it.

7/23/2007 12:42 PM | 10668844
Gravatar

# re: Data belongs in your tables -- not in your code

The reason developers don't do this all the time in practice is simple and unfortunate.

Releasing database updates along with code releases can get a bit cumbersome, so I find developers try to minimize the amount of database changes they need to make in order to deploy their solution.

It sucks for those of us afterwards trying to write meaningful reports off of data, but I've been in the field long enough to know that developers take that shortcut quite often; it saves them a lot of pain despite of making life easier afterwards.
7/23/2007 12:47 PM | Costas
Gravatar

# re: Data belongs in your tables -- not in your code

I agree on examples #1,#2, and #3, but disagree on #4 since it is declaring "policy" in the database rather than in the business logic. It's far more likely over time that the policy regarding status codes will change rather than the translation from the letter PK into the full description. Business rules belong in the business tier where they can be more easily integrated into dynamic workflows.
7/23/2007 1:15 PM | Keith Williams
Gravatar

# re: Data belongs in your tables -- not in your code

Keith --

>>It's far more likely over time that the policy regarding status codes will change rather than the translation from the letter PK into the full description.

Exactly my point! yet, we store the description of status code 'A' in the database and we can easily change it at any time without recompiling or changing a single line of code. Even though it rarely changes. Yet adding user types or status code or altering permissions happens all the time -- and it will require code rewrites and re-releases each time if you embedd this logic all into your code! that is why you put it in a table along with your data and changing and documenting *exactly* what a super-user can do is a simple act of editing or querying data.

You've made my point quite well! :)
7/23/2007 2:11 PM | Jeff
Gravatar

# re: Data belongs in your tables -- not in your code

You all are right but.

Being a system designer on datawarehousing and accountkeeping field I would say it depends.
It depends on many factors regarding the circumstances of the dispatcher of the query and the database:
This an application querying his dedicated database
This is an adhoc report from a legacy system
This is an extension application to a legacy system
This is regular report on a DW
and many-many more.


There is always a choice between parametrization and hardcoding. You cannot say that hardcoding parameters is not the right way. You have to choose based on the estimates of code changing's possibility, resources available and complexity.
If you think something is a parameter, then you assume a structure of this parameters and you can be wrong in the future. The code is then harder to maintain, because the parameter structure and the handling code must be altered, too.
If you hardcode something, then you assume skalar values that do not change, that can be an error if the requirements change rapidly.

It is far from trivial for example, that you have an option of creating a table anywhere.
Suppose you have to do a quick adhoc report on a legacy system. You choose to have parameter tables. Where do you put that table? All production systems are isolated in a firewalled virtual subnet, and you only have sql command line access to the production environment. You cannot touch production systems without a minimum one day long QA process. It is not acceptable to deliver the data in two days. If the boss asks for this report every week then it is another story.
And this is a real world example.

Other examples in my inherited code base are very complicated parameter structures that are reflecting 6 year old business structures, that were passe after half year after the production launch.


7/23/2007 4:02 PM | Daniel
Gravatar

# Sorting suggestion

Regarding the sort scheme trick in Example 2:

When creating a "sort" column in a table (which I prefer to call "priority"), I always make it a FLOAT, because once you have it populated with values such as 1, 2, 3, 4, etc., you can always insert new records and make them sort among the others without having to edit all records -- just use a value in between the existing ones, such as 1.5, 1.2, 1.25, 1.255. Constructing SQL to modify an existing sort scheme is also very trivial, requiring a minimal load on the server. After much re-ordering, you may end up with no integral values, but it doesn't really matter since it's for internal use only. You are more likely to exhaust disk space long before running into any precision limits.

I use this concept anywhere that an alpha sort on a display value is unacceptable.
7/23/2007 4:42 PM | Andrew Moyer
Gravatar

# re: Data belongs in your tables -- not in your code

The key here is defining "data".

When applications are distributed to a fresh install they should function flawlessly with an empty database. If the database has to be "primed" with all sorts of lookup data before the application can function then that's a red flag that a handful of best practices have been violated.

When the process needs to store and retrieve data it can use an RDBMS or any other number of data storage devices. If the application cannot function without specific sets of data in specific tables of a specific type of database then the application is too highly coupled to the database.

"Data" does not come into existence on its own merits - it's always the by-product of some process or group of processes somewhere. These processes (collectively the "business logic") are defined and executed within one or more applications or services. Therefore, a "Customer" is data about a person that has gone through a commerce process, but a "CustomerType" is a business-logic tag assigned by the specific commerce process that the customer went through. Therefore "Customer" belongs in the database, and "CustomerType" belongs in the process.


7/23/2007 5:06 PM | Henry
Gravatar

# re: Data belongs in your tables -- not in your code

Great simple advice. Now I understand why I always feel a little dirty when I use case statements.
7/23/2007 6:20 PM | Mike
Gravatar

# re: Data belongs in your tables -- not in your code

Excellent post. Thank-you.

The shift thing is something I ran into years ago. Being inexperienced with dbs (still am really) adding the period-of-validity to the pk never occurred to me. The best I could come up with at the time was using this definition table to 'mint' new shift data as and when required, which would have dumped the logic into app-space or triggers.

Your approach is much better
7/23/2007 10:13 PM | gregb
Gravatar

# re: Data belongs in your tables -- not in your code

Why does your proposed StatusCodes table eschew normalisation?
7/24/2007 9:13 AM | gwenhwyfaer
Gravatar

# re: Data belongs in your tables -- not in your code

gwenhwyfaer -- If you tell me *how* it "eschew's normalisation", I'll tell you why. Deal?

7/24/2007 9:24 AM | Jeff
Gravatar

# re: Data belongs in your tables -- not in your code

Nice Article Jeff.
I almost didn't read it cuz I thought "No Sugar, Sherlock". But was ashamed to realize that I've been doing this in a lot of procs I've been writing recently. (Being the sole support for a Data Warehouse make one forget good practice)

Funny thing is, a few years ago when I was developing software on a big team, this would have been second nature to me. I need to code review my work now!

BTW, Gwen's right about your status codes table. You should have something like:

create table StatusCodeUsage (usage varchar not null, description varchar null)
insert into StatusCodeUsage (usage,description) values ('isValid','description here')
insert into StatusCodeUsage (usage,description) values ('IsEditable','description here')
etc

then create an xref table associating them:

create table xrefSC_SCUsage (StatusCode varchar not null,Usage varchar not null)
insert into xrefSC_SCUsage (StatusCode,Usage) values ('A','isValid')
insert into xrefSC_SCUsage (StatusCode,Usage) values ('H','isValid')
insert into xrefSC_SCUsage (StatusCode,Usage) values ('A','IsEditable')
insert into xrefSC_SCUsage (StatusCode,Usage) values ('P','IsEditable')
insert into xrefSC_SCUsage (StatusCode,Usage) values ('R','IsEditable')

Now you can add new Status Code groupings without altering your schema.
7/24/2007 3:51 PM | Bob
Gravatar

# re: Data belongs in your tables -- not in your code

Thanks, Bob. that's one way of storing the status code attributes if you like, and certainly an option, but it doesn't make things any more normalized than storing them as columns.

The disadvantage to breaking things out into columns like that? We now have 'isValid' and 'isEditable' values in our database which must be bound to our application code. Binding column and table names to our code is par for the course; the goal here is to avoid binding database contents to our code if we can. Anyway, you can do it either way, it depends on how dynamic your applications needs to be.

Another useful way to use Status Codes that I've done in the past is to create an action table of what can be done with each status code, and what the resulting status code is. For example:

StatusCode, Action, ResultingCode
'A','Delete','D'
'P','Activate','A'
'A','Archive',R'

etc ... you can easily incorporate this same concept to a "permissions" table as well, and the intersection of available actions plus what your permissions allows is what you can do to an object for the given status. The app can then display the list of valid actions, and when the user selects an option (via a button press or however you want to physically display it in the UI) it simply follows the attributes set in the table and updates the status code to the new value.

Thanks for the feedback!!
7/24/2007 4:03 PM | Jeff
Gravatar

# re: Data belongs in your tables -- not in your code

There have to be some fixed points, sure - but whilst I'm all in favour of minimising them, being dogmatic about where they are doesn't help anyone. A far more useful delineation than data / metadata is between behavioural data and representative data (suggestions for better names are welcome), where the former is always safe to refer to from within code, and can encompass not only the metadata we usually deal with in queries but also schemes like the one Bob suggests (which is what I had in mind; thanks, Bob). The key here is that the permissions (and other behavioural data) are only given meaning by the code that interprets them; yet the indirection keeps that data separated from data which merely represents information - interactions between the two are tightly controlled by association lists like the one Bob describes.

As far as normalisation goes, the presence of nulls in the table (even if they look like 0s, they are NULLs semantically) is a dead giveaway. According to Wikipedia, it is "non-first normal form" - the applicable permissions comprise a set - but that's an explicitly denormalised form. Hence my comment.
7/24/2007 9:37 PM | gwenhwyfaer
Gravatar

# re: Data belongs in your tables -- not in your code

>>being dogmatic about where they are doesn't help anyone.

Where am I being dogmatic? Can you do me a favor: recap, in a sentence or two, what you think I am saying in this article.

>>As far as normalisation goes, the presence of nulls in the table (even if they look like 0s, they are NULLs semantically) is a dead giveaway. According to Wikipedia, it is "non-first normal form" - the applicable permissions comprise a set - but that's an explicitly denormalised form. Hence my comment.

Presence of nulls? A bit/boolean value of 0 is a "NULL semantically" ? I don't even know how to respond ...

As for your other points, that is exactly correct -- the right place for information depends on if it determines behavior for a single specific application or if it applies to your system/data as a whole. Sounds like you could use some database 101, but at least you get the key concept I am talking about here and in the other post.
7/24/2007 10:12 PM | Jeff
Gravatar

# re: Data belongs in your tables -- not in your code

gwenhwyfaer -- just quick note before you write your recap for me ... I'll help you out a little ... Here are the things I am *not* saying in this post, or any other:

* NEVER reference ANY data values in your CODE
* ALWAYS put ANYTHING that isn't strictly CODE in a database
* NEVER use config files! Put it in a database!
* NEVER use constants! Put it in a databse!
* ALWAYS put status codes and permission in a database, NEVER anywhere else, regardless of your design or specifications!
* ALWAYS make your applications COMPLETELY generic so that EVERY action that can occur is COMPLETELY defined in a database!

It is simple. You have a database. You are using it to store data. It works well for that purpose. Just be sure to take advantage if you can. That's really it. (Quite dogmatic, indeed!)

Isn't this pretty basic stuff? I don't think I am breaking any new ground here. It's funny how people like to imagine things just so that they can debate or argue ...
7/24/2007 10:31 PM | Jeff
Gravatar

# re: Data belongs in your tables -- not in your code

I have nothing to add to the conversation other than to say "EXACTLY!" - Nice article.
7/25/2007 8:06 AM | Travis Laborde
Gravatar

# re: Data belongs in your tables -- not in your code

> Where am I being dogmatic?

The title of your post sounded pretty dogmatic to me.

FYI, I've posted an extended reply in my blog.
7/25/2007 1:40 PM | Ron
Gravatar

# re: Data belongs in your tables -- not in your code

Thank You.......
8/29/2008 10:34 AM | rüya tabiri
Gravatar

# re: Data belongs in your tables -- not in your code

I strongly agree, assuming you have the ability to define additonal data tables. Working with vendor applications you can encounter retrictions on creating additional tables. My primary assignment is to develop data extracts and reports and I am limited to the use of read-only views, functions and stored procedures. Where the application vendor has not provided a decode table I define a function containing a case statement. While not as flexible as a data table, at least the values and and logic is contained in a single location.
12/26/2008 4:25 PM | Ken Kibbe
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET