Sunday, March 23, 2008

Of Tables and Constraints

Hello and welcome to the Database programmer blog. This is a blog for anybody who wants to learn about databases on their own terms and find out how database decisions affect their applications.

Constraints are probably the least talked about topic in application and database design. I suspect that there is something deep in the souls of programmers that just does not like to talk about limitations. We like to talk about flexibility, extensions, abstraction and so forth. Constraints rub us wrong. But of course we also love to write absolute rules for ourselves and others (thou shalt always use integer primary keys, thou shalt not use HTML TABLE elements, and so on). So go figure.

If you want to make a deadline, you have to limit what you do. You cannot make a list a kilometer long and finish it in a day. By the same principle, if a database is going to accurately store data, there must be rules about what makes the data correct. These rules we call constraints.

This essay is part of the Keys, Normalization and Denormalization series. There is also a Complete Table of Contents and a Skills-oriented Table Of Contents.

Table Structure is The First Constraint

Table structure is not usually listed as a constraint, but your table structures represent the first and most basic constraints on your application.

The table structure can be pictured as the mission statement of the application. If you have a table of students, we can safely assume you must record certain facts about students. Likewise, if you do not have a table of Nobel Prize Winners, we can safely assume that Novel Laureates are outside the scope of your application.

This basic fact, that table structure represents a codification of design decisions, is worth getting into a little deeper.

Where Do You Want The Zebra?

The importance of table structures can be seen with an analogy. Imagine you purchase a building and some printing equipment, and then you hire some people and create a printing shop. Somebody comes in and says, "Where do you want this zebra?" The answer is that you have no need or use for a zebra in a printing shop, and it will actually get in the way and cause problems. So the answer is "Not here!" There is a constraint on the use of your building, which is that wild game is not welcome.

Database tables represent that decision to commit to a certain task or collection of tasks. They constrain the end users to perform the mission of the company, and not some other mission.

Sometimes the more naive programmers will not give up on this point, and they will respond with something like "Well, yes, but computers do not have the limitations of physical systems, they are so much more flexible." This is true but wrong. A computer language like C++ is incredibly flexible in its potential, but that potential is worth nothing until somebody sits down and writes code to do a specific task, at which point that code is good only for that task. In other words, the general purpose value of the computer is only realized when dedicated to a specific purpose. Likewise, a database server is in principle capable of storing data about anything, but is only useful once decisions have been made to create specific tables to store specific things.

To say it one more time, table designs are constraints because they say what will be tracked and what will not be tracked.

Primary Keys, Unique Constraints and Foreign Keys

The next category of constraints have been well discussed on this blog, and I do not want to spend too much time on them here, but we will do a quick review.

Your table designs are the foundation of your application. In simple terms, a well normalized database will have a table for every different kind of thing that must be tracked (students, courses, classrooms, etc). In addition, there are always plenty of cross references that list relationships between these primary entries (teacher to courses they are qualified to teach), and transactions that represent interactions between these (which courses a student has completed).

The primary key makes sure that there are no duplicates, and the foreign key ensures that data stored in separate tables makes sense. For instance, imagine a table that lists which courses each teacher is qualifed to teach. It would not make much sense to list teachers in this table that are not on the faculty, and the foreign key prevents these kinds of mistakes.

As a veteran database designer I am still often astonished at how many seemingly complicated and unique business requirements reduce to a simple collection of tables, with their primary and foreign keys. I have seen this happen so many times that I am now automatically skeptical when somebody tells me they have 'special needs' that probably will not fit into simple tables. They always end up fitting into tables.

Check Constraints

Once you have your tables and their keys, there is one more kind of constraint you can use, which is called the CHECK constraint. A check constraint is nothing more than some SQL expression that must always be true, and is attached either to a column or a table.

MySQL does not support CHECK constraints as of version 5.0. This is one of the reasons why users of other database systems often say nasty things about MySQL.

A typical example for a check constraint is that a discount price must be lower than a regular price. Another use is to make sure that a number is between one and 100, so that it can be used as a percentage. This bit of SQL illustrates these ideas in the PostgreSQL dialect:

CREATE SEQUENCE orderlines_line
CREATE TABLE ORDERLINES (
   order int references orders (order)
   ,line int DEFAULT nextval('orderlines_line')
   ,sku char(10) references items (sku)
   ,price numeric(10,2)
   ,discount_price numeric(10,2) CHECK (discount_price < price)
   ,tax_pct numeric(3) CHECK (tax_pct >= 0 AND tax_pct <= 100)
)

Like all server-side technologies, the CHECK constraint can greatly reduce the amount of client-side code you have to write. Going further, the CHECK constraint makes your application easier to port to other languages, if for no other reason than there is less code to port.

More Complex Constraints

Sometimes constraints are more complex than can be expressed with a CHECK constraint. In these cases you can attach a "trigger" to a table that fires when a row is written. The trigger can modify the data being written or prevent the write from happening. Triggers are a large topic so they will be treated separately in other essays. Like all other computer technologies, triggers have plenty of fans who quietly use them to great affect, while there are plenty of noisy types telling you your nose will fall off if you use them.

Conclusion

This week we have seen that constraints are used to help to ensure that all data going into a database is correct. Constraints begin with the basic decisions about tables themselves, and then go through the selection of keys, and can be more complex CHECK constraints or even be completely general purpose code-based rules enforced in Triggers.

Other Posts

This essay is part of the Keys, Normalization and Denormalization series. There is also a Complete Table of Contents and a Skills-oriented Table Of Contents.

14 comments:

The Marketer said...

I enjoyed your blog Thanks for sharing such an informative post. We are also providing the best services click on below links to visit our website.

digital marketing company in nagercoil
digital marketing services in nagercoil
digital marketing agency in nagercoil
best marketing services in nagercoil
SEO company in nagercoil
SEO services in nagercoil
social media marketing in nagercoil
social media company in nagercoil
PPC services in nagercoil
digital marketing company in velachery
digital marketing company in velachery
digital marketing services in velachery
digital marketing agency in velachery
SEO company in velachery
SEO services in velachery
social media marketing in velachery
social media company in velachery
PPC services in velachery
online advertisement services in velachery
online advertisement services in nagercoil

web design company in nagercoil
web development company in nagercoil
website design company in nagercoil
website development company in nagercoil
web designing company in nagercoil
website designing company in nagercoil
best web design company in nagercoil
web design company in velachery
web development company in velachery
website design company in velachery
website development company in velachery
web designing company in velachery
website designing company in velachery
best web design company in velachery

Thanks for Sharing - ( Groarz branding solutions )

Susan said...

Discover the most popular people ranked on Celebrity birthdays.

yahanvideonet said...


I have recently started a web site, the info you provide on this site has helped me greatly. Thanks for all of your time & work. Feel free to visit my website;

yadongbiz said...

That is a very good tip especially to those new to the blogosphere. Short but very accurate info… Appreciate your sharing this one. A must read post. Feel free to visit my website; 일본야동

koreayadongcom said...

I was very pleased to discover this website. I wanted to thank you for your time for this fantastic read!! I definitely liked every part of it and i also have you saved as a favorite to see new stuff on your website. Feel free to visit my website;
국산야동

chinayadongnet said...

This is very interesting, You're a very skilled blogger. I've joined your feed and look forward to seeking more of your great post. Also, I have shared your web site in my social networks! Feel free to visit my website;
일본야동

japanyadongcom said...

I stumble on this site when I was making research on my project topic. I found some useful topics in well detail form. Thanks for this excellent post. Feel free to visit my website;
한국야동

periyannan said...

This is a great post. I like this topic.This site has lots of advantage.I found many interesting things from this site. It helps me in many ways.Thanks for posting this again.
internship for web development | internship in electrical engineering | mini project topics for it 3rd year | online internship with certificate | final year project for cse

casinotrựctuyến said...

I came to this site with the introduction of a friend around me and I was very impressed when I found your writing. I'll come back often after bookmarking! casino trực tuyến

슬롯사이트 said...

I am a 슬롯사이트 expert. I've read a lot of articles, but I'm the first person to understand as well as you. I leave a post for the first time. It's great!!

sportstoto365 said...

This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. I will visit your blog regularly for Some latest post. 토토

powerballsite said...

This is a smart blog. I mean it. You have so much knowledge about this issue, and so much passion. You also know how to make people rally behind it, obviously from the responses. 파워볼게임

슬롯사이트 said...

I've been searching for hours on this topic and finally found your post. 슬롯사이트 , I have read your post and I am very impressed. We prefer your opinion and will visit this site frequently to refer to your opinion. When would you like to visit my site?

온라인바카라사이트 said...

Hello, you used to write excellent, but the last few posts have been kinda boring… I miss your tremendous writings. Past few posts are just a little bit out of track! 온라인바카라사이트