DATABASE DESIGN CONCEPTS AND SQL STANDARDS

Database Fundamentals

noreply@blogger.com (அசுரன் திராவிடன்) — Thu, 08 Apr 2010 02:33:00 +0000

The Following are Basic Facts about databases.

Database Terms
Attribute Cardinality Data Dictionary DBMS Engine Design Tools Attribute's Domain Entity Entity Class Father of Relational Databases Foreign Key	Hierarchy of Data Elements Meta Data Overhead Data Primary Key Relation Relational Database Runtime Subsystem Schema Transactions User Data

ATTRIBUTE
An attribute is another word for field. In spreadsheet language it would be a cell. It is a place in a database table to store one piece of data of a given type. For example an attribute designated to hold a last_name, could hold "Smith", but should not hold "Amy Smith".

CARDINALITY
Cardinality is a way to express minimum value and maximum value which are governed by the business rules. Cardinality refers to the required number of instances an entity must have in order to make the in a relationship in order for it to be valid. Minimum cardinality then for a one to many relationship would be one. Minimum cardinality for a basketball team would be 5, or you would be forced to forfeit the game. Maximum cardinality is the maximum number of entities which can occur in a relationship in order for it to be valid. In a one to one relationship the maximum cardinality would also be one. For a baseball team, during the normal season, the maximum cardinality would be 25 active players on the roster.

THE DATA DICTIONARY
A database is self describing. By this we mean it documents itself through table structure outputs. One of the components of the data dictionary is the table data type layout.

You can easily see how the fields are defined. The data types, lengths of the fields, and if they can be null or not. This is just one example of the data dictionary information provided by a DBMS.

THE DBMS ENGINE
This is a component of the DBMS (Database Management System) which is the intermediary between the design tools and run-time sub-systems and the data. The DBMS engine receives requests form the other two components, which is presented in column and row format, translates them into commands which are passed to the operating system in order to provide read and write functions to the disk.

THE DESIGN TOOLS
One of the three components of a DBMS. This subsystem provides the tools to assist users and programmers in creating and modifying components of the database. such components are, Tables, Queries, Reports, and User Forms. Many DBMS products provide a programming environment to create databases which perform very complex tasks.

Attribute's DOMAIN
The domain of an attribute is the range of data it can contain. This is not to say the attribute can contain the entire range at one time. An attributes contents must be atomic, meaning they must be of a single bit of information about the theme of the record. For example an attribute named "JOB_TITLE", from the EMPLOYEES table, could contain values from "Machine Operator", "Driver", "Foreman","Shift Manager", all the way up to "President". It can only hold one of these per record at a time. An attribute designated for "JOB_TITLE" cannot hold any other type data, such as Salary, or Date_Of_Hire. Can you imagine having to look for the Date_Of_Hire somewhere in a table, but having no specified place? You might as well be searching text files again.

ENTITY
A entity is something that someone wants to track. An employee for example. It is basically the subject for a table. You gather data about the employee, you run queries to find out information about them, track their time, vacation, sick days etc... Therefore, an entity is very much the same as a record in a table.relational databases was E.F. Codd, who worked for IBM at that time. He published a paper titled "A Relational Model of Data for Large Shared Databanks" in June of 1970.

ENTITY CLASS
A entity class is a collection of entities, as defined by their structure. There are usually many entities in an entity class, all of the same structure and type. In my mind, an entity class is the table which contains the entities.

Father of Relational Databases.
The father of

FOREIGN KEY
A foreign key is the same data field and type which is linked to a primary key in a corresponding table. For example in a transaction table The Customer_ID would be the Foreign Key field. The Foreign Key is used to look up the Customer_ID in the customer table where the Customer_ID is the primary key.

The Hierarchy of Data Elements in:

A file processing system
1. Bits->Bytes or Characters->Fields->Records->Files
A Database system
1. Bits->Bytes or Characters->Fields->Records->Files+Metadata + Indexes + App MetaData.

META DATA
Meta data is the data about data. In the above example concerning the DATA DICTIONARY. Is an example of meta data. It is the self describing part of a database. Information such as the table names, user names, data types, and field sizes are all meta data, describing the database.

OVERHEAD DATA
Overhead data is that which the system uses for itself. Indexes for example are overhead data. This is because the system uses indexes to speed searches, and to aid in joins. The overhead part comes in that this data also consumes processing time, and resources. Each time you update a table, the index must also be updated, which takes a bit of processing time but it also speeds up the search capability. You have to decide if the price in resources is worth the benefit of speed in creating and maintaining an index.

PRIMARY KEY
The primary key is that field, or fields, which by itself, or together uniquely identify each row in a table. The Primary Key is usually indexed, in some systems that is required. The primary key is normally the field or combined fields by which joins are linked. All data within each row or record should be dependent on the entirety of the primary key. Primary Keys are used to normalize data tables.

A RELATION
This is a table which, as one of its attributes has a unique identifier for each of it's records, also known as a primary key. In most cases, the primary key is indexed to enhance performance of the system by speeding the lookup capabilities of the DBMS.

In the above example you can see the Product_ID is unique for each item. This entire table, with the unique field is called a relation.

A RELATIONAL DATABASE
Relational database we entails some forms of data relationship. It gets its name for it's relation to other tables within the database. A relational database is set up so that the a key is presented in two or more tables. In one table it will be the primary key, however, in the other table it will be the foreign key. Where the primary key matches the foreign key is where the relationship occurs. You may have a one to one relation where only one of each key members can be present in each table. You may also have a one to many relationship, where only one member can exist in one table but many occurrences can be present in the other table. An finally you can have a many to many relationship, where many occurrences can be present in both tables. Below is an example of a one to many relationship. There is only one occurrence of the Product_id in the products table, But many occurrences can exist in the transaction table.layout of the tables, attribute types and sizes, which fields are indexed, the relationships, domains, and business rules concerning a database. It is the design from which the database as well as its application programs were built. In a nutshell, the schema encompasses everything about the database.entire function. For example a sales transaction. You need the following.

THE RUNTIME SUBSYSTEM
This subsystem processes the application components that are developed using the design tools. For example Access has a runtime component that links data to forms, and reports. This is just part of the DBMS. The user or the developer need be concerned with how it works. When a given form is opened the runtime subsystem opens the required tables extracts the data and displays it to the user. There is also a component that facilitates the read and write requests for the applications.

SCHEMA
The SCHEMA is the design of the database, and why it was created. The schema is the

TRANSACTIONS
Transactions are a group of sql statements which work together to perform an

A statement to add a record to the transaction table.
A statement update the Inventory Table.
A statement update the customer table. If necessary.
A statement to commit the data.

One of two things MUST happen. All of these statements must work together to accomplish their goal, or none of the statements work. That is the key to transaction processing, all or nothing. Log files are kept by the system to record what has been accomplished so, in the event something goes wrong, we know where to start. This is a way of maintaining the integrity of our data.

USER DATA
User data is just as the name implies. User data is the data which the user enters into the database tables.

Introduction to Relational Database Management System

noreply@blogger.com (அசுரன் திராவிடன்) — Thu, 08 Apr 2010 02:05:00 +0000

Ten Common Database Design Mistakes

noreply@blogger.com (அசுரன் திராவிடன்) — Mon, 05 Apr 2010 02:09:00 +0000

by Louis Davidson

No list of mistakes is ever going to be exhaustive. People (myself included) do a lot of really stupid things, at times, in the name of "getting it done." This list simply reflects the database design mistakes that are currently on my mind, or in some cases, constantly on my mind. I have done this topic two times before. If you're interested in hearing the podcast version, visit Greg Low's super-excellent SQL Down Under. I also presented a boiled down, ten-minute version at PASS for the Simple-Talk booth. Originally there were ten, then six, and today back to ten. And these aren't exactly the same ten that I started with; these are ten that stand out to me as of today.

Before I start with the list, let me be honest for a minute. I used to have a preacher who made sure to tell us before some sermons that he was preaching to himself as much as he was to the congregation. When I speak, or when I write an article, I have to listen to that tiny little voice in my head that helps filter out my own bad habits, to make sure that I am teaching only the best practices. Hopefully, after reading this article, the little voice in your head will talk to you when you start to stray from what is right in terms of database design practices.

So, the list:

Poor design/planning
Ignoring normalization
Poor naming standards
Lack of documentation
One table to hold all domain values
Using identity/guid columns as your only key
Not using SQL facilities to protect data integrity
Not using stored procedures to access data
Trying to build generic objects

Lack of testing

Poor design/planning

"If you don't know where you are going, any road will take you there" – George Harrison
Prophetic words for all parts of life and a description of the type of issues that plague many projects these days.
Let me ask you: would you hire a contractor to build a house and then demand that they start pouring a foundation the very next day? Even worse, would you demand that it be done without blueprints or house plans? Hopefully, you answered "no" to both of these. A design is needed make sure that the house you want gets built, and that the land you are building it on will not sink into some underground cavern. If you answered yes, I am not sure if anything I can say will help you.
Like a house, a good database is built with forethought, and with proper care and attention given to the needs of the data that will inhabit it; it cannot be tossed together in some sort of reverse implosion.
Since the database is the cornerstone of pretty much every business project, if you don't take the time to map out the needs of the project and how the database is going to meet them, then the chances are that the whole project will veer off course and lose direction. Furthermore, if you don't take the time at the start to get the database design right, then you'll find that any substantial changes in the database structures that you need to make further down the line could have a huge impact on the whole project, and greatly increase the likelihood of the project timeline slipping.
Far too often, a proper planning phase is ignored in favor of just "getting it done". The project heads off in a certain direction and when problems inevitably arise – due to the lack of proper designing and planning – there is "no time" to go back and fix them properly, using proper techniques. That's when the "hacking" starts, with the veiled promise to go back and fix things later, something that happens very rarely indeed.
Admittedly it is impossible to predict every need that your design will have to fulfill and every issue that is likely to arise, but it is important to mitigate against potential problems as much as possible, by careful planning.

Ignoring Normalization

Normalization defines a set of methods to break down tables to their constituent parts until each table represents one and only one "thing", and its columns serve to fully describe only the one "thing" that the table represents.
The concept of normalization has been around for 30 years and is the basis on which SQL and relational databases are implemented. In other words, SQL was created to work with normalized data structures. Normalization is not just some plot by database programmers to annoy application programmers (that is merely a satisfying side effect!)
SQL is very additive in nature in that, if you have bits and pieces of data, it is easy to build up a set of values or results. In the FROM clause, you take a set of data (a table) and add (JOIN) it to another table. You can add as many sets of data together as you like, to produce the final set you need.
This additive nature is extremely important, not only for ease of development, but also for performance. Indexes are most effective when they can work with the entire key value. Whenever you have to use SUBSTRING, CHARINDEX, LIKE, and so on, to parse out a value that is combined with other values in a single column (for example, to split the last name of a person out of a full name column) the SQL paradigm starts to break down and data becomes become less and less searchable.
So normalizing your data is essential to good performance, and ease of development, but the question always comes up: "How normalized is normalized enough?" If you have read any books about normalization, then you will have heard many times that 3rd Normal Form is essential, but 4th and 5th Normal Forms are really useful and, once you get a handle on them, quite easy to follow and well worth the time required to implement them.
In reality, however, it is quite common that not even the first Normal Form is implemented correctly.
Whenever I see a table with repeating column names appended with numbers, I cringe in horror. And I cringe in horror quite often. Consider the following example Customer table:

Are there always 12 payments? Is the order of payments significant? Does a NULL value for a payment mean UNKNOWN (not filled in yet), or a missed payment? And when was the payment made?!?
A payment does not describe a Customer and should not be stored in the Customer table. Details of payments should be stored in a Payment table, in which you could also record extra information about the payment, like when the payment was made, and what the payment was for:

In this second design, each column stores a single unit of information about a single "thing" (a payment), and each row represents a specific instance of a payment.
This second design is going to require a bit more code early in the process but, it is far more likely that you will be able to figure out what is going on in the system without having to hunt down the original programmer and kick their butt…sorry… figure out what they were thinking

Poor naming standards

"That which we call a rose, by any other name would smell as sweet"
This quote from Romeo and Juliet by William Shakespeare sounds nice, and it is true from one angle. If everyone agreed that, from now on, a rose was going to be called dung, then we could get over it and it would smell just as sweet. The problem is that if, when building a database for a florist, the designer calls it dung and the client calls it a rose, then you are going to have some meetings that sound far more like an Abbott and Costello routine than a serious conversation about storing information about horticulture products.
Names, while a personal choice, are the first and most important line of documentation for your application. I will not get into all of the details of how best to name things here– it is a large and messy topic. What I want to stress in this article is the need for consistency. The names you choose are not just to enable you to identify the purpose of an object, but to allow all future programmers, users, and so on to quickly and easily understand how a component part of your database was intended to be used, and what data it stores. No future user of your design should need to wade through a 500 page document to determine the meaning of some wacky name.
Consider, for example, a column named, X304_DSCR. What the heck does that mean? You might decide, after some head scratching, that it means "X304 description". Possibly it does, but maybe DSCR means discriminator, or discretizator?
Unless you have established DSCR as a corporate standard abbreviation for description, then X304_DESCRIPTION is a much better name, and one leaves nothing to the imagination.
That just leaves you to figure out what the X304 part of the name means. On first inspection, to me, X304 sounds like more like it should be data in a column rather than a column name. If I subsequently found that, in the organization, there was also an X305 and X306 then I would flag that as an issue with the database design. For maximum flexibility, data is stored in columns, not in column names.
Along these same lines, resist the temptation to include "metadata" in an object's name. A name such as tblCustomer or colVarcharAddress might seem useful from a development perspective, but to the end user it is just confusing. As a developer, you should rely on being able to determine that a table name is a table name by context in the code or tool, and present to the users clear, simple, descriptive names, such as Customer and Address.
A practice I strongly advise against is the use of spaces and quoted identifiers in object names. You should avoid column names such as "Part Number" or, in Microsoft style, [Part Number], therefore requiring you users to include these spaces and identifiers in their code. It is annoying and simply unnecessary.
Acceptable alternatives would be part_number, partNumber or PartNumber. Again, consistency is key. If you choose PartNumber then that's fine – as long as the column containing invoice numbers is called InvoiceNumber, and not one of the other possible variations.

Lack of documentation

I hinted in the intro that, in some cases, I am writing for myself as much as you. This is the topic where that is most true. By carefully naming your objects, columns, and so on, you can make it clear to anyone what it is that your database is modeling. However, this is only step one in the documentation battle. The unfortunate reality is, though, that "step one" is all too often the only step.
Not only will a well-designed data model adhere to a solid naming standard, it will also contain definitions on its tables, columns, relationships, and even default and check constraints, so that it is clear to everyone how they are intended to be used. In many cases, you may want to include sample values, where the need arose for the object, and anything else that you may want to know in a year or two when "future you" has to go back and make changes to the code.
NOTE:
Where this documentation is stored is largely a matter of corporate standards and/or convenience to the developer and end users. It could be stored in the database itself, using extended properties. Alternatively, it might be in maintained in the data modeling tools. It could even be in a separate data store, such as Excel or another relational database. My company maintains a metadata repository database, which we developed in order to present this data to end users in a searchable, linkable format. Format and usability is important, but the primary battle is to have the information available and up to date.
Your goal should be to provide enough information that when you turn the database over to a support programmer, they can figure out your minor bugs and fix them (yes, we all make bugs in our code!). I know there is an old joke that poorly documented code is a synonym for "job security." While there is a hint of truth to this, it is also a way to be hated by your coworkers and never get a raise. And no good programmer I know of wants to go back and rework their own code years later. It is best if the bugs in the code can be managed by a junior support programmer while you create the next new thing. Job security along with raises is achieved by being the go-to person for new challenges.

One table to hold all domain values

"One Ring to rule them all and in the darkness bind them"
This is all well and good for fantasy lore, but it's not so good when applied to database design, in the form of a "ruling" domain table. Relational databases are based on the fundamental idea that every object represents one and only one thing. There should never be any doubt as to what a piece of data refers to. By tracing through the relationships, from column name, to table name, to primary key, it should be easy to examine the relationships and know exactly what a piece of data means.
The big myth perpetrated by architects who don't really understand relational database architecture (me included early in my career) is that the more tables there are, the more complex the design will be. So, conversely, shouldn't condensing multiple tables into a single "catch-all" table simplify the design? It does sound like a good idea, but at one time giving Pauly Shore the lead in a movie sounded like a good idea too.
For example, consider the following model snippet where I needed domain values for:

Customer CreditStatus
Customer Type
Invoice Status
Invoice Line Item BackOrder Status
Invoice Line Item Ship Via Carrier

On the face of it that would be five domain tables…but why not just use one generic domain table, like this?

This may seem a very clean and natural way to design a table for all but the problem is that it is just not very natural to work with in SQL. Say we just want the domain values for the Customer table:

 
SELECT *
FROM Customer
  JOIN GenericDomain as CustomerType
    ON Customer.CustomerTypeId = CustomerType.GenericDomainId
      and CustomerType.RelatedToTable = 'Customer'
      and  CustomerType.RelatedToColumn = 'CustomerTypeId'
  JOIN GenericDomain as CreditStatus
    ON  Customer.CreditStatusId = CreditStatus.GenericDomainId
      and CreditStatus.RelatedToTable = 'Customer'
      and CreditStatus.RelatedToColumn = ' CreditStatusId'

As you can see, this is far from being a natural join. It comes down to the problem of mixing apples with oranges. At first glance, domain tables are just an abstract concept of a container that holds text. And from an implementation centric standpoint, this is quite true, but it is not the correct way to build a database. In a database, the process of normalization, as a means of breaking down and isolating data, takes every table to the point where one row represents one thing. And each domain of values is a distinctly different thing from all of the other domains (unless it is not, in which case the one table will suffice.). So what you do, in essence, is normalize the data on each usage, spreading the work out over time, rather than doing the task once and getting it over with.
So instead of the single table for all domains, you might model it as:

Looks harder to do, right? Well, it is initially. Frankly it took me longer to flesh out the example tables. But, there are quite a few tremendous gains to be had:

Using the data in a query is much easier:

SELECT *
FROM Customer
  JOIN CustomerType
    ON Customer.CustomerTypeId = CustomerType.CustomerTypeId
  JOIN CreditStatus
    ON  Customer.CreditStatusId = CreditStatus.CreditStatusId

Data can be validated using foreign key constraints very naturally, something not feasible for the other solution unless you implement ranges of keys for every table – a terrible mess to maintain.
If it turns out that you need to keep more information about a ShipViaCarrier than just the code, 'UPS', and description, 'United Parcel Service', then it is as simple as adding a column or two. You could even expand the table to be a full blown representation of the businesses that are carriers for the item.
All of the smaller domain tables will fit on a single page of disk. This ensures a single read (and likely a single page in cache). If the other case, you might have your domain table spread across many pages, unless you cluster on the referring table name, which then could cause it to be more costly to use a non-clustered index if you have many values.
You can still have one editor for all rows, as most domain tables will likely have the same base structure/usage. And while you would lose the ability to query all domain values in one query easily, why would you want to? (A union query could easily be created of the tables easily if needed, but this would seem an unlikely need.)

I should probably rebut the thought that might be in your mind. "What if I need to add a new column to all domain tables?" For example, you forgot that the customer wants to be able to do custom sorting on domain values and didn't put anything in the tables to allow this. This is a fair question, especially if you have 1000 of these tables in a very large database. First, this rarely happens, and when it does it is going to be a major change to your database in either way.
Second, even if this became a task that was required, SQL has a complete set of commands that you can use to add columns to tables, and using the system tables it is a pretty straightforward task to build a script to add the same column to hundreds of tables all at once. That will not be as easy of a change, but it will not be so much more difficult to outweigh the large benefits.
The point of this tip is simply that it is better to do the work upfront, making structures solid and maintainable, rather than trying to attempt to do the least amount of work to start out a project. By keeping tables down to representing one "thing" it means that most changes will only affect one table, after which it follows that there will be less rework for you down the road.

Using identity/guid columns as your only key

First Normal Form dictates that all rows in a table must be uniquely identifiable. Hence, every table should have a primary key. SQL Server allows you to define a numeric column as an IDENTITY column, and then automatically generates a unique value for each row. Alternatively, you can use NEWID() (or NEWSEQUENTIALID()) to generate a random, 16 byte unique value for each row. These types of values, when used as keys, are what are known as surrogate keys. The word surrogate means "something that substitutes for" and in this case, a surrogate key should be the stand-in for a natural key.
The problem is that too many designers use a surrogate key column as the only key column on a given table. The surrogate key values have no actual meaning in the real world; they are just there to uniquely identify each row.
Now, consider the following Part table, whereby PartID is an IDENTITY column and is the primary key for the table:

PartID	PartNumber	Description
1	XXXXXXXX	The X part
2	XXXXXXXX	The X part
3	YYYYYYYY	The Y part

How many rows are there in this table? Well, there seem to be three, but are rows with PartIDs 1 and 2 actually the same row, duplicated? Or are they two different rows that should be unique but were keyed in incorrectly?
The rule of thumb I use is simple. If a human being could not pick which row they want from a table without knowledge of the surrogate key, then you need to reconsider your design. This is why there should be a key of some sort on the table to guarantee uniqueness, in this case likely on PartNumber.
In summary: as a rule, each of your tables should have a natural key that means something to the user, and can uniquely identify each row in your table. In the very rare event that you cannot find a natural key (perhaps, for example, a table that provides a log of events), then use an artificial/surrogate key.

Not using SQL facilities to protect data integrity

All fundamental, non-changing business rules should be implemented by the relational engine. The base rules of nullability, string length, assignment of foreign keys, and so on, should all be defined in the database.
There are many different ways to import data into SQL Server. If your base rules are defined in the database itself can you guarantee that they will never be bypassed and you can write your queries without ever having to worry whether the data you're viewing adheres to the base business rules.
Rules that are optional, on the other hand, are wonderful candidates to go into a business layer of the application. For example, consider a rule such as this: "For the first part of the month, no part can be sold at more than a 20% discount, without a manager's approval".
Taken as a whole, this rule smacks of being rather messy, not very well controlled, and subject to frequent change. For example, what happens when next week the maximum discount is 30%? Or when the definition of "first part of the month" changes from 15 days to 20 days? Most likely you won't want go through the difficulty of implementing these complex temporal business rules in SQL Server code – the business layer is a great place to implement rules like this.
However, consider the rule a little more closely. There are elements of it that will probably never change. E.g.

The maximum discount it is ever possible to offer
The fact that the approver must be a manager

These aspects of the business rule very much ought to get enforced by the database and design. Even if the substance of the rule is implemented in the business layer, you are still going to have a table in the database that records the size of the discount, the date it was offered, the ID of the person who approved it, and so on. On the Discount column, you should have a CHECK constraint that restricts the values allowed in this column to between 0.00 and 0.90 (or whatever the maximum is). Not only will this implement your "maximum discount" rule, but will also guard against a user entering a 200% or a negative discount by mistake. On the ManagerID column, you should place a foreign key constraint, which reference the Managers table and ensures that the ID entered is that of a real manager (or, alternatively, a trigger that selects only EmployeeIds corresponding to managers).
Now, at the very least we can be sure that the data meets the very basic rules that the data must follow, so we never have to code something like this in order to check that the data is good:

 SELECT CASE WHEN discount < 0 then 0 else WHEN discount > 1 then 1…

We can feel safe that data meets the basic criteria, every time.

Not using stored procedures to access data

Stored procedures are your friend. Use them whenever possible as a method to insulate the database layer from the users of the data. Do they take a bit more effort? Sure, initially, but what good thing doesn't take a bit more time? Stored procedures make database development much cleaner, and encourage collaborative development between your database and functional programmers. A few of the other interesting reasons that stored procedures are important include the following.

Maintainability

Stored procedures provide a known interface to the data, and to me, this is probably the largest draw. When code that accesses the database is compiled into a different layer, performance tweaks cannot be made without a functional programmer's involvement. Stored procedures give the database professional the power to change characteristics of the database code without additional resource involvement, making small changes, or large upgrades (for example changes to SQL syntax) easier to do.

Encapsulation

Stored procedures allow you to "encapsulate" any structural changes that you need to make to the database so that the knock on effect on user interfaces is minimized. For example, say you originally modeled one phone number, but now want an unlimited number of phone numbers. You could leave the single phone number in the procedure call, but store it in a different table as a stopgap measure, or even permanently if you have a "primary" number of some sort that you always want to display. Then a stored proc could be built to handle the other phone numbers. In this manner the impact to the user interfaces could be quite small, while the code of stored procedures might change greatly.

Security

Stored procedures can provide specific and granular access to the system. For example, you may have 10 stored procedures that all update table X in some way. If a user needs to be able to update a particular column in a table and you want to make sure they never update any others, then you can simply grant to that user the permission to execute just the one procedure out of the ten that allows them perform the required update.

Performance

There are a couple of reasons that I believe stored procedures enhance performance. First, if a newbie writes ratty code (like using a cursor to go row by row through an entire ten million row table to find one value, instead of using a WHERE clause), the procedure can be rewritten without impact to the system (other than giving back valuable resources.) The second reason is plan reuse. Unless you are using dynamic SQL calls in your procedure, SQL Server can store a plan and not need to compile it every time it is executed. It's true that in every version of SQL Server since 7.0 this has become less and less significant, as SQL Server gets better at storing plans ad hoc SQL calls (see note below). However, stored procedures still make it easier for plan reuse and performance tweaks. In the case where ad hoc SQL would actually be faster, this can be coded into the stored procedure seamlessly.
In 2005, there is a database setting (PARAMETERIZATION FORCED) that, when enabled, will cause all queries to have their plans saved. This does not cover more complicated situations that procedures would cover, but can be a big help. There is also a feature known as plan guides, which allow you to override the plan for a known query type. Both of these features are there to help out when stored procedures are not used, but stored procedures do the job with no tricks.
And this list could go on and on. There are drawbacks too, because nothing is ever perfect. It can take longer to code stored procedures than it does to just use ad hoc calls. However, the amount of time to design your interface and implement it is well worth it, when all is said and done.

Trying to code generic T-SQL objects

I touched on this subject earlier in the discussion of generic domain tables, but the problem is more prevalent than that. Every new T-SQL programmer, when they first start coding stored procedures, starts to think "I wish I could just pass a table name as a parameter to a procedure." It does sound quite attractive: one generic stored procedure that can perform its operations on any table you choose. However, this should be avoided as it can be very detrimental to performance and will actually make life more difficult in the long run.
T-SQL objects do not do "generic" easily, largely because lots of design considerations in SQL Server have clearly been made to facilitate reuse of plans, not code. SQL Server works best when you minimize the unknowns so it can produce the best plan possible. The more it has to generalize the plan, the less it can optimize that plan.
Note that I am not specifically talking about dynamic SQL procedures. Dynamic SQL is a great tool to use when you have procedures that are not optimizable / manageable otherwise. A good example is a search procedure with many different choices. A precompiled solution with multiple OR conditions might have to take a worst case scenario approach to the plan and yield weak results, especially if parameter usage is sporadic.
However, the main point of this tip is that you should avoid coding very generic objects, such as ones that take a table name and twenty column names/value pairs as a parameter and lets you update the values in the table. For example, you could write a procedure that started out:

 
CREATE PROCEDURE updateAnyTable
@tableName sysname,
@columnName1 sysname,
@columnName1Value varchar(max)
@columnName2 sysname,
@columnName2Value varchar(max)
…

The idea would be to dynamically specify the name of a column and the value to pass to a SQL statement. This solution is no better than simply using ad hoc calls with an UPDATE statement. Instead, when building stored procedures, you should build specific, dedicated stored procedures for each task performed on a table (or multiple tables.) This gives you several benefits:

Properly compiled stored procedures can have a single compiled plan attached to it and reused.
Properly compiled stored procedures are more secure than ad-hoc SQL or even dynamic SQL procedures, reducing the surface area for an injection attack greatly because the only parameters to queries are search arguments or output values.
Testing and maintenance of compiled stored procedures is far easier to do since you generally have only to search arguments, not that tables/columns/etc exist and handling the case where they do not

A nice technique is to build a code generation tool in your favorite programming language (even T-SQL) using SQL metadata to build very specific stored procedures for every table in your system. Generate all of the boring, straightforward objects, including all of the tedious code to perform error handling that is so essential, but painful to write more than once or twice.
In my Apress book, Pro SQL Server 2005 Database Design and Optimization, I provide several such "templates" (manly for triggers, abut also stored procedures) that have all of the error handling built in, I would suggest you consider building your own (possibly based on mine) to use when you need to manually build a trigger/procedure or whatever.

Lack of testing

When the dial in your car says that your engine is overheating, what is the first thing you blame? The engine. Why don't you immediately assume that the dial is broken? Or something else minor? Two reasons:

The engine is the most important component of the car and it is common to blame the most important part of the system first.
It is all too often true.

As database professionals know, the first thing to get blamed when a business system is running slow is the database. Why? First because it is the central piece of most any business system, and second because it also is all too often true.
We can play our part in dispelling this notion, by gaining deep knowledge of the system we have created and understanding its limits through testing.
But let's face it; testing is the first thing to go in a project plan when time slips a bit. And what suffers the most from the lack of testing? Functionality? Maybe a little, but users will notice and complain if the "Save" button doesn't actually work and they cannot save changes to a row they spent 10 minutes editing. What really gets the shaft in this whole process is deep system testing to make sure that the design you (presumably) worked so hard on at the beginning of the project is actually implemented correctly.
But, you say, the users accepted the system as working, so isn't that good enough? The problem with this statement is that what user acceptance "testing" usually amounts to is the users poking around, trying out the functionality that they understand and giving you the thumbs up if their little bit of the system works. Is this reasonable testing? Not in any other industry would this be vaguely acceptable. Do you want your automobile tested like this? "Well, we drove it slowly around the block once, one sunny afternoon with no problems; it is good!" When that car subsequently "failed" on the first drive along a freeway, or during the first drive through rain or snow, then the driver would have every right to be very upset.
Too many database systems get tested like that car, with just a bit of poking around to see if individual queries and modules work. The first real test is in production, when users attempt to do real work. This is especially true when it is implemented for a single client (even worse when it is a corporate project, with management pushing for completion more than quality).
Initially, major bugs come in thick and fast, especially performance related ones. If the first time you have tried a full production set of users, background process, workflow processes, system maintenance routines, ETL, etc, is on your system launch day, you are extremely likely to discover that you have not anticipated all of the locking issues that might be caused by users creating data while others are reading it, or hardware issues cause by poorly set up hardware. It can take weeks to live down the cries of "SQL Server can't handle it" even after you have done the proper tuning.
Once the major bugs are squashed, the fringe cases (which are pretty rare cases, like a user entering a negative amount for hours worked) start to raise their ugly heads. What you end up with at this point is software that irregularly fails in what seem like weird places (since large quantities of fringe bugs will show up in ways that aren't very obvious and are really hard to find.)
Now, it is far harder to diagnose and correct because now you have to deal with the fact that users are working with live data and trying to get work done. Plus you probably have a manager or two sitting on your back saying things like "when will it be done?" every 30 seconds, even though it can take days and weeks to discover the kinds of bugs that result in minor (yet important) data aberrations. Had proper testing been done, it would never have taken weeks of testing to find these bugs, because a proper test plan takes into consideration all possible types of failures, codes them into an automated test, and tries them over and over. Good testing won't find all of the bugs, but it will get you to the point where most of the issues that correspond to the original design are ironed out.
If everyone insisted on a strict testing plan as an integral and immutable part of the database development process, then maybe someday the database won't be the first thing to be fingered when there is a system slowdown.

Summary

Database design and implementation is the cornerstone of any data centric project (read 99.9% of business applications) and should be treated as such when you are developing. This article, while probably a bit preachy, is as much a reminder to me as it is to anyone else who reads it. Some of the tips, like planning properly, using proper normalization, using a strong naming standards and documenting your work– these are things that even the best DBAs and data architects have to fight to make happen. In the heat of battle, when your manager's manager's manager is being berated for things taking too long to get started, it is not easy to push back and remind them that they pay you now, or they pay you later. These tasks pay dividends that are very difficult to quantify, because to quantify success you must fail first. And even when you succeed in one area, all too often other minor failures crop up in other parts of the project so that some of your successes don't even get noticed.
The tips covered here are ones that I have picked up over the years that have turned me from being mediocre to a good data architect/database programmer. None of them take extraordinary amounts of time (except perhaps design and planning) but they all take more time upfront than doing it the "easy way". Let's face it, if the easy way were that easy in the long run, I for one would abandon the harder way in a second. It is not until you see the end result that you realize that success comes from starting off right as much as finishing right.

Document your Database. One of the common database design mistakes highlighted by Louis Davidson in this article is "lack of documentation". SQL Doc, Red Gate's database documentation tool, will significantly ease the task of generating and maintaining accurate database documentation, a traditionally time-consuming and error-prone process.

Fundamentals of Relational Database Design

noreply@blogger.com (அசுரன் திராவிடன்) — Sat, 03 Apr 2010 02:09:00 +0000

Overview

Database design theory is a topic that many people avoid learning for lack of time. Many others attempt to learn it, but give up because of the dry, academic treatment it is usually given by most authors and teachers. But if creating databases is part of your job, then you're treading on thin ice if you don't have a good solid understanding of relational database design theory. This article begins with an introduction to relational database design theory, including a discussion of keys, relationships, integrity rules, and the often-dreaded "Normal Forms." Following the theory, I present a practical step-by-step approach to good database design.

The Relational Model

The relational database model was conceived by E. F. Codd in 1969, then a researcher at IBM. The model is based on branches of mathematics called set theory and predicate logic. The basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables. This model was in vast contrast to the more traditional database theories of the time that were much more complicated, less flexible and dependent on the physical storage methods of the data. Note: It is commonly thought that the word relational in the relational model comes from the fact that you relate together tables in a relational database. Although this is a convenient way to think of the term, it's not accurate. Instead, the word relational has its roots in the terminology that Codd used to define the relational model. The table in Codd's writings was actually referred to as a relation (a related set of information). In fact, Codd (and other relational database theorists) use the terms relations, attributes and tuples where most of us use the more common terms tables, columns and rows, respectively (or the more physical—and thus less preferable for discussions of database design theory—files, fields and records). The relational model can be applied to both databases and database management systems (DBMS) themselves. The relational fidelity of database programs can be compared using Codd's 12 rules (since Codd's seminal paper on the relational model, the number of rules has been expanded to 300) for determining how DBMS products conform to the relational model. When compared with other database management programs, Microsoft Access fares quite well in terms of relational fidelity. Still, it has a long way to go before it meets all twelve rules completely. Fortunately, you don't have to wait until Microsoft Access is perfect in a relational sense before you can benefit from the relational model. The relational model can also be applied to the design of databases, which is the subject of the remainder of this article.

Relational Database Design

When designing a database, you have to make decisions regarding how best to take some system in the real world and model it in a database. This consists of deciding which tables to create, what columns they will contain, as well as the relationships between the tables. While it would be nice if this process was totally intuitive and obvious, or even better automated, this is simply not the case. A well-designed database takes time and effort to conceive, build and refine. The benefits of a database that has been designed according to the relational model are numerous. Some of them are:

Data entry, updates and deletions will be efficient.
Data retrieval, summarization and reporting will also be efficient.
Since the database follows a well-formulated model, it behaves predictably.
Since much of the information is stored in the database rather than in the application, the database is somewhat self-documenting.
Changes to the database schema are easy to make.

The goal of this article is to explain the basic principles behind relational database design and demonstrate how to apply these principles when designing a database using Microsoft Access. This article is by no means comprehensive and certainly not definitive. Many books have been written on database design theory; in fact, many careers have been devoted to its study. Instead, this article is meant as an informal introduction to database design theory for the database developer. Note: While the examples in this article are centered around Microsoft Access databases, the discussion also applies to database development using the Microsoft Visual Basic® programming system, the Microsoft FoxPro® database management system, and the Microsoft SQL Server™ client-server database management system.

Tables, Uniqueness and Keys

Tables in the relational model are used to represent "things" in the real world. Each table should represent only one thing. These things (or entities) can be real-world objects or events. For example, a real-world object might be a customer, an inventory item, or an invoice. Examples of events include patient visits, orders, and telephone calls. Tables are made up of rows and columns. The relational model dictates that each row in a table be unique. If you allow duplicate rows in a table, then there's no way to uniquely address a given row via programming. This creates all sorts of ambiguities and problems that are best avoided. You guarantee uniqueness for a table by designating a primary key—a column that contains unique values for a table. Each table can have only one primary key, even though several columns or combination of columns may contain unique values. All columns (or combination of columns) in a table with unique values are referred to as candidate keys, from which the primary key must be drawn. All other candidate key columns are referred to as alternate keys. Keys can be simple or composite. A simple key is a key made up of one column, whereas a composite key is made up of two or more columns. The decision as to which candidate key is the primary one rests in your hands—there's no absolute rule as to which candidate key is best. Fabian Pascal, in his book SQL and Relational Basics, notes that the decision should be based upon the principles of minimality (choose the fewest columns necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users). Let's illustrate with an example. Say that a company has a table of customers called tblCustomer, which looks like the table shown in Figure 1.

Figure 1. The best choice for primary key for tblCustomer would be CustomerId.

Candidate keys for tblCustomer might include CustomerId, (LastName + FirstName), Phone#, (Address, City, State), and (Address + ZipCode). Following Pascal's guidelines, you would rule out the last three candidates because addresses and phone numbers can change fairly frequently. The choice among CustomerId and the name composite key is less obvious and would involve tradeoffs. How likely would a customer's name change (e.g., marriages cause names to change)? Will misspelling of names be common? How likely will two customers have the same first and last names? How familiar will CustomerId be to users? There's no right answer, but most developers favor numeric primary keys because names do sometimes change and because searches and sorts of numeric columns are more efficient than of text columns in Microsoft Access (and most other databases). Counter columns in Microsoft Access make good primary keys, especially when you're having trouble coming up with good candidate keys, and no existing arbitrary identification number is already in place. Don't use a counter column if you'll sometimes need to renumber the values—you won't be able to—or if you require an alphanumeric code—Microsoft Access supports only long integer counter values. Also, counter columns only make sense for tables on the one side of a one-to-many relationship (see the discussion of relationships in the next section). Note: In many situations, it is best to use some sort of arbitrary static whole number (e.g., employee ID, order ID, a counter column, etc.) as a primary key rather than a descriptive text column. This avoids the problem of misspellings and name changes. Also, don't use real numbers as primary keys since they are inexact.

Foreign Keys and Domains

Although primary keys are a function of individual tables, if you created databases that consisted of only independent and unrelated tables, you'd have little need for them. Primary keys become essential, however, when you start to create relationships that join together multiple tables in a database. A foreign key is a column in a table used to reference a primary key in another table. Continuing the example presented in the last section, let's say that you choose CustomerId as the primary key for tblCustomer. Now define a second table, tblOrder, as shown in Figure 2.

Figure 2. CustomerId is a foreign key in tblOrder which can be used to reference a customer stored in the tblCustomer table.

CustomerId is considered a foreign key in tblOrder since it can be used to refer to given customer (i.e., a row in the tblCustomer table). It is important that both foreign keys and the primary keys that are used to reference share a common meaning and draw their values from the same domain. Domains are simply pools of values from which columns are drawn. For example, CustomerId is of the domain of valid customer ID #'s, which in this case might be Long Integers ranging between 1 and 50,000. Similarly, a column named Sex might be based on a one-letter domain equaling 'M' or 'F'. Domains can be thought of as user-defined column types whose definition implies certain rules that the columns must follow and certain operations that you can perform on those columns. Microsoft Access supports domains only partially. For example, Microsoft Access will not let you create a relationship between two tables using columns that do not share the same datatype (e.g., text, number, date/time, etc.). On the other hand, Microsoft Access will not prevent you from joining the Integer column EmployeeAge from one table to the Integer column YearsWorked from a second table, even though these two columns are obviously from different domains.

Relationships

You define foreign keys in a database to model relationships in the real world. Relationships between real-world entities can be quite complex, involving numerous entities each having multiple relationships with each other. For example, a family has multiple relationships between multiple people—all at the same time. In a relational database such as Microsoft Access, however, you consider only relationships between pairs of tables. These tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many.

One-to-One Relationships

Two tables are related in a one-to-one (1—1) relationship if, for every row in the first table, there is at most one row in the second table. True one-to-one relationships seldom occur in the real world. This type of relationship is often created to get around some limitation of the database management software rather than to model a real-world situation. In Microsoft Access, one-to-one relationships may be necessary in a database when you have to split a table into two or more tables because of security or performance concerns or because of the limit of 255 columns per table. For example, you might keep most patient information in tblPatient, but put especially sensitive information (e.g., patient name, social security number and address) in tblConfidential (see Figure 3). Access to the information in tblConfidential could be more restricted than for tblPatient. As a second example, perhaps you need to transfer only a portion of a large table to some other application on a regular basis. You can split the table into the transferred and the non-transferred pieces, and join them in a one-to-one relationship.

Figure 3. The tables tblPatient and tblConfidential are related in a one-to-one relationship. The primary key of both tables is PatientId.

Tables that are related in a one-to-one relationship should always have the same primary key, which will serve as the join column.

One-to-Many Relationships

Two tables are related in a one-to-many (1—M) relationship if for every row in the first table, there can be zero, one, or many rows in the second table, but for every row in the second table there is exactly one row in the first table. For example, each order for a pizza delivery business can have multiple items. Therefore, tblOrder is related to tblOrderDetails in a one-to-many relationship (see Figure 4). The one-to-many relationship is also referred to as a parent-child or master-detail relationship. One-to-many relationships are the most commonly modeled relationship.

Figure 4. There can be many detail lines for each order in the pizza delivery business, so tblOrder and tblOrderDetails are related in a one-to-many relationship.

One-to-many relationships are also used to link base tables to information stored in lookup tables. For example, tblPatient might have a short one-letter DischargeDiagnosis code, which can be linked to a lookup table, tlkpDiagCode, to get more complete Diagnosis descriptions (stored in DiagnosisName). In this case, tlkpDiagCode is related to tblPatient in a one-to-many relationship (i.e., one row in the lookup table can be used in zero or more rows in the patient table).

Many-to-Many Relationships

Two tables are related in a many-to-many (M—M) relationship when for every row in the first table, there can be many rows in the second table, and for every row in the second table, there can be many rows in the first table. Many-to-many relationships can't be directly modeled in relational database programs, including Microsoft Access. These types of relationships must be broken into multiple one-to-many relationships. For example, a patient may be covered by multiple insurance plans and a given insurance company covers multiple patients. Thus, the tblPatient table in a medical database would be related to the tblInsurer table in a many-to-many relationship. In order to model the relationship between these two tables, you would create a third, linking table, perhaps called tblPtInsurancePgm that would contain a row for each insurance program under which a patient was covered (see Figure 5). Then, the many-to-many relationship between tblPatient and tblInsurer could be broken into two one-to-many relationships (tblPatient would be related to tblPtInsurancePgm and tblInsurer would be related to tblPtInsurancePgm in one-to-many relationships).

Figure 5. A linking table, tblPtInsurancePgm, is used to model the many-to-many relationship between tblPatient and tblInsurer.

In Microsoft Access, you specify relationships using the Edit—Relationships command. In addition, you can create ad-hoc relationships at any point, using queries.

Normalization

As mentioned earlier in this article, when designing databases you are faced with a series of choices. How many tables will there be and what will they represent? Which columns will go in which tables? What will the relationships between the tables be? The answers each to these questions lies in something called normalization. Normalization is the process of simplifying the design of a database so that it achieves the optimum structure. Normalization theory gives us the concept of normal forms to assist in achieving the optimum structure. The normal forms are a linear progression of rules that you apply to your database, with each higher normal form achieving a better, more efficient design. The normal forms are:

First Normal Form
Second Normal Form
Third Normal Form
Boyce Codd Normal Form
Fourth Normal Form
Fifth Normal Form

In this article I will discuss normalization through Third Normal Form.

Before First Normal Form: Relations

The Normal Forms are based on relations rather than tables. A relation is a special type of table that has the following attributes:

They describe one entity.
They have no duplicate rows; hence there is always a primary key.
The columns are unordered.
The rows are unordered.

Microsoft Access doesn't require you to define a primary key for each and every table, but it strongly recommends it. Needless to say, the relational model makes this an absolute requirement. In addition, tables in Microsoft Access generally meet attributes 3 and 4. That is, with a few exceptions, the manipulation of tables in Microsoft Access doesn't depend upon a specific ordering of columns or rows. (One notable exception is when you specify the data source for a combo or list box.) For all practical purposes the terms table and relation are interchangeable, and I will use the term table in the remainder of this chapter. It's important to note, however, that when I use the term table, I actually mean a table that also meets the definition of a relation.

First Normal Form

First Normal Form (1NF) says that all column values must be atomic.

The word atom comes from the Latin atomis, meaning indivisible (or literally "not to cut"). 1NF dictates that, for every row-by-column position in a given table, there exists only one value, not an array or list of values. The benefits from this rule should be fairly obvious. If lists of values are stored in a single column, there is no simple way to manipulate those values. Retrieval of data becomes much more laborious and difficult to generalize. For example, the table in Figure 6, tblOrder1, used to store order records for a hardware store, would violate 1NF:

Figure 6. tblOrder1 violates First Normal Form because the data stored in the Items column is not atomic.

You'd have a difficult time retrieving information from this table, because too much information is being stored in the Items field. Think how difficult it would be to create a report that summarized purchases by item. 1NF also prohibits the presence of repeating groups, even if they are stored in composite (multiple) columns. For example, the same table might be improved upon by replacing the single Items column with six columns: Quant1, Item1, Quant2, Item2, Quant3, Item3 (see Figure 7).

Figure 7. A better, but still flawed, version of the Orders table, tblOrder2. The repeating groups of information violate First Normal Form.

While this design has divided the information into multiple fields, it's still problematic. For example, how would you go about determining the quantity of hammers ordered by all customers during a particular month? Any query would have to search all three Item columns to determine if a hammer was purchased and then sum over the three quantity columns. Even worse, what if a customer ordered more than three items in a single order? You could always add additional columns, but where would you stop? Ten items, twenty items? Say that you decided that a customer would never order more than twenty-five items in any one order and designed the table accordingly. That means you would be using 50 columns to store the item and quantity information per record, even for orders that only involved one or two items. Clearly this is a waste of space. And someday, someone would want to order more than 25 items. Tables in 1NF do not have the problems of tables containing repeating groups. The table in Figure 8, tblOrder3, is 1NF since each column contains one value and there are no repeating groups of columns. In order to attain 1NF, I have added a column, OrderItem#. The primary key of this table is a composite key made up of OrderId and OrderItem#.

Figure 8. The tblOrder3 table is in First Normal Form.

You could now easily construct a query to calculate the number of hammers ordered. The query in Figure 9 is an example of such a query.

Figure 9. Since tblOrder3 is in First Normal Form, you can easily construct a Totals query to determine the total number of hammers ordered by customers.

Second Normal Form

A table is said to be in Second Normal Form (2NF), if it is in 1NF and every non-key column is fully dependent on the (entire) primary key.

Put another way, tables should only store data relating to one "thing" (or entity) and that entity should be described by its primary key. The table shown in Figure 10, tblOrder4, is slightly modified version of tblOrder3. Like tblOrder3, tblOrder4 is in First Normal Form. Each column is atomic, and there are no repeating groups.

Figure 10. The tblOrder4 table is in First Normal Form. Its primary key is a composite of OrderId and OrderItem#.

To determine if tblOrder4 meets 2NF, you must first note its primary key. The primary key is a composite of OrderId and OrderItem#. Thus, in order to be 2NF, each non-key column (i.e., every column other than OrderId and OrderItem#) must be fully dependent on the primary key. In other words, does the value of OrderId and OrderItem# for a given record imply the value of every other column in the table? The answer is no. Given the OrderId, you know the customer and date of the order, without having to know the OrderItem#. Thus, these two columns are not dependent on the entire primary key which is composed of both OrderId and OrderItem#. For this reason tblOrder4 is not 2NF. You can achieve Second Normal Form by breaking tblOrder4 into two tables. The process of breaking a non-normalized table into its normalized parts is called decomposition. Since tblOrder4 has a composite primary key, the decomposition process is straightforward. Simply put everything that applies to each order in one table and everything that applies to each order item in a second table. The two decomposed tables, tblOrder and tblOrderDetail, are shown in Figure 11.

Figure 11. The tblOrder and tblOrderDetail tables satisfy Second Normal Form. OrderId is a foreign key in tblOrderDetail that you can use to rejoin the tables.

Two points are worth noting here.

When normalizing, you don't throw away information. In fact, this form of decomposition is termed non-loss decomposition because no information is sacrificed to the normalization process.
You decompose the tables in such a way as to allow them to be put back together again using queries. Thus, it's important to make sure that tblOrderDetail contains a foreign key to tblOrder. The foreign key in this case is OrderId which appears in both tables.

Third Normal Form

A table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent.

An obvious example of a dependency is a calculated column. For example, if a table contains the columns Quantity and PerItemCost, you could opt to calculate and store in that same table a TotalCost column (which would be equal to Quantity*PerItemCost), but this table wouldn't be 3NF. It's better to leave this column out of the table and make the calculation in a query or on a form or a report instead. This saves room in the database and avoids having to update TotalCost, every time Quantity or PerItemCost changes. Dependencies that aren't the result of calculations can also exist in a table. The tblOrderDetail table from Figure 11, for example, is in 2NF because all of its non-key columns (Quantity, ProductId and ProductDescription) are fully dependent on the primary key. That is, given an OderID and an OrderItem#, you know the values of Quantity, ProductId and ProductDescription. Unfortunately, tblOrderDetail also contains a dependency among two if its non-key columns, ProductId and ProductDescription. Dependencies cause problems when you add, update, or delete records. For example, say you need to add 100 detail records, each of which involves the purchase of screwdrivers. This means you would have to input a ProductId code of 2 and a ProductDescription of "screwdriver" for each of these 100 records. Clearly this is redundant. Similarly, if you decide to change the description of the item to "No. 2 Phillips-head screwdriver" at some later time, you will have to update all 100 records. Another problem arises when you wish to delete all of the 1994 screwdriver purchase records at the end of the year. Once all of the records are deleted, you will no longer know what ProductId of 2 is, since you've deleted from the database both the history of purchases and the fact that ProductId 2 means "No. 2 Phillips-head screwdriver." You can remedy each of these anomalies by further normalizing the database to achieve Third Normal Form. Note: An Anomaly is simply an error or inconsistency in the database. A poorly designed database runs the risk of introducing numerous anomalies. There are three types of anomalies:

Insertion: an anomaly that occurs during the insertion of a record. For example, the insertion of a new row causes a calculated total field stored in another table to report the wrong total.
Deletion: an anomaly that occurs during the deletion of a record. For example, the deletion of a row in the database deletes more information than you wished to delete.
Update: an anomaly that occurs during the updating of a record. For example, updating a description column for a single part in an inventory database requires you to make a change to thousands of rows.

The tblOrderDetail table can be further decomposed to achieve 3NF by breaking out the ProductId—ProductDescription dependency into a lookup table as shown in Figure 12. This gives you a new order detail table, tblOrderDetail1 and a lookup table, tblProduct. When decomposing tblOrderDetail, take care to put a copy of the linking column, in this case ProductId, in both tables. ProductId becomes the primary key of the new table, tblProduct, and becomes a foreign key column in tblOrderDetail1. This allows you to easily join together the two tables using a query.

Figure 12. The tbOrderDetail1 and tblProduct tables are in Third Normal Form. The ProductId column in tblOrderDetail1 is a foreign key referencing tblProduct.

Higher Normal Forms

After Codd defined the original set of normal forms it was discovered that Third Normal Form, as originally defined, had certain inadequacies. This led to several higher normal forms, including the Boyce/Codd, Fourth and Fifth Normal Forms. I will not be covering these higher normal forms, instead, several points are worth noting here:

Every higher normal form is a superset of all lower forms. Thus, if your design is in Third Normal Form, by definition it is also in 1NF and 2NF.
If you've normalized your database to 3NF, you've likely also achieved Boyce/Codd Normal Form (and maybe even 4NF or 5NF).
To quote C.J. Date, the principles of database design are "nothing more than formalized common sense."
Database design is more art than science.

This last item needs to be emphasized. While it's relatively easy to work through the examples in this article, the process gets more difficult when you are presented with a business problem (or another scenario) that needs to be computerized (or downsized). I have outlined an approach to take later in this article, but first the subject of integrity rules will be discussed.

Integrity Rules

The relational model defines several integrity rules that, while not part of the definition of the Normal Forms are nonetheless a necessary part of any relational database. There are two types of integrity rules: general and database-specific.

General Integrity Rules

The relational model specifies two general integrity rules. They are referred to as general rules, because they apply to all databases. They are: entity integrity and referential integrity. The entity integrity rule is very simple. It says that primary keys cannot contain null (missing) data. The reason for this rule should be obvious. You can't uniquely identify or reference a row in a table, if the primary key of that table can be null. It's important to note that this rule applies to both simple and composite keys. For composite keys, none of the individual columns can be null. Fortunately, Microsoft Access automatically enforces the entity integrity rule for you. No component of a primary key in Microsoft Access can be null. The referential integrity rule says that the database must not contain any unmatched foreign key values. This implies that:

A row may not be added to a table with a foreign key unless the referenced value exists in the referenced table.
If the value in a table that's referenced by a foreign key is changed (or the entire row is deleted), the rows in the table with the foreign key must not be "orphaned."

In general, there are three options available when a referenced primary key value changes or a row is deleted. The options are:

Disallow. The change is completely disallowed.
Cascade. For updates, the change is cascaded to all dependent tables. For deletions, the rows in all dependent tables are deleted.
Nullify. For deletions, the dependent foreign key values are set to Null.

Microsoft Access allows you to disallow or cascade referential integrity updates and deletions using the Edit | Relationships command (see Figure 13). Nullify is not an option.

Figure 13. Specifying a relationship with referential integrity between the tblCustomer and tblOrder tables using the Edit | Relationships command. Updates of CustomerId in tblCustomer will be cascaded to tblOrder. Deletions of rows in tblCustomer will be disallowed if rows in tblOrders would be orphaned.

Note: When you wish to implement referential integrity in Microsoft Access, you must perform one additional step outside of the Edit | Relationships dialog: in table design, you must set the Required property for the foreign key column to Yes. Otherwise, Microsoft Access will allow your users to enter a Null foreign key value, thus violating strict referential integrity.

Database-Specific Integrity Rules

All integrity constraints that do not fall under entity integrity or referential integrity are termed database-specific rules or business rules. These type of rules are specific to each database and come from the rules of the business being modeled by the database. It is important to note that the enforcement of business rules is as important as the enforcement of the general integrity rules discussed in the previous section. Note: Rules in Microsoft Access 2.0 are now enforced at the engine level, which means that forms, action queries and table imports can no longer ignore your rules. Because of this change, however, column rules can no longer reference other columns or use domain, aggregate, or user-defined functions. Without the specification and enforcement of business rules, bad data will get in the database. The old adage, "garbage in, garbage out" applies aptly to the application (or lack of application) of business rules. For example, a pizza delivery business might have the following rules that would need to be modeled in the database:

Order date must always be between the date the business started and the current date.
Order time and delivery time can be only during business hours.
Delivery time must be greater than or equal to Order time.
New orders cannot be created for discontinued menu items.
Customer zip codes must be within a certain range—the delivery area.
The quantity ordered can never be less than 1 or greater than 50.
Non-null discounts can never be less than 1 percent or greater than 30 percent.

Microsoft Access 2.0 supports the specification of validation rules for each column in a table. For example, the first business rule from the above list has been specified in Figure 14.

Figure 14. A column validation rule has been created to limit all order dates to some time between the first operating day of the business (5/3/93) and the current date.

Microsoft Access 2.0 also supports the specification of a global rule that applies to the entire table. This is useful for creating rules that cross-reference columns as the example in Figure 15 demonstrates. Unfortunately, you're only allowed to create one global rule per table, which could make for some awful validation error messages (e.g., "You have violated one of the following rules: 1. Delivery Date > Order Date. 2. Delivery Time > Order Time....").

Figure 15. A table validation rule has been created to require that deliveries be made on or after the date the pizza was ordered.

Although Microsoft Access business-rule support is better than most other desktop DBMS programs, it is still limited (especially the limitation of one global table rule), so you will typically build additional business rule logic into applications, usually in the data entry forms. This logic should be layered on top of any table-based rules and can be built into the application using combo boxes, list-boxes and option groups that limit available choices, form-level and field-level validation rules, and event procedures. These application-based rules, however, should be used only when the table-based rules cannot do the job. The more you can build business rules in at the table level, the better, because these rules will always be enforced and will require less maintenance.

A Practical Approach to Database Design

As mentioned earlier in this article, database design is more art than science. While it's true that a properly designed database should follow the normal forms and the relational model, you still have to come up with a design that reflects the business you are trying to model. Relational database design theory can usually tell you what not to do, but it won't tell you where to start or how to manage your business. This is where it helps to understand the business (or other scenario) you are trying to model. A well-designed database requires business insight, time, and experience. Above all, it shouldn't be rushed. To assist you in the creation of databases, I've outlined the following 20-step approach to sound database design:

Take some time to learn the business (or other system) you are trying to model. This will usually involve sitting down and meeting with the people who will be using the system and asking them lots of questions.
On paper, write out a basic mission statement for the system. For example, you might write something like "This system will be used to take orders from customers and track orders for accounting and inventory purposes." In addition, list out the requirements of the system. These requirements will guide you in creating the database schema and business rules. For example, create a list that includes entries such as "Must be able to track customer address for subsequent direct mail."
Start to rough out (on paper) the data entry forms. (If rules come to mind as you lay out the tables, add them to the list of requirements outlined in step 2.) The specific approach you take will be guided by the state of any existing system.
- If this system was never before computerized, take the existing paper-based system and rough out the table design based on these forms. It's very likely that these forms will be non-normalized.
- If the database will be converted from an existing computerized system, use its tables as a starting point. Remember, however, that it's very likely that the existing schema will be non-normalized. It's much easier to normalize the database now rather than later. Print out the existing schema, table by table, and the existing data entry forms to use in the design process.
- If you are really starting from scratch (e.g., for a brand new business), then rough out on paper what forms you envision filling out.
Based on the forms, you created in step 3, rough out your tables on paper. If normalization doesn't come naturally (or from experience), you can start by creating one huge, non-normalized table per form that you will later normalize. If you're comfortable with normalization theory, try and keep it in mind as you create your tables, remembering that each table should describe a single entity.
Look at your existing paper or computerized reports. (If you're starting from scratch, rough out the types of reports you'd like to see on paper.) For existing systems that aren't currently meeting the user needs, it's likely that key reports are missing. Create them now on paper.
Take the roughed-out reports from step 5 and make sure that the tables from step 4 include this data. If information is not being collected, add it to the existing tables or create new ones.
On paper, add several rows to each roughed-out table. Use real data if at all possible.
Start the normalization process. First, identify candidate keys for every table and using the candidates, choose the primary key. Remember to choose a primary key that is minimal, stable, simple, and familiar. Every table must have a primary key! Make sure that the primary key will guard against all present and future duplicate entries.
Note foreign keys, adding them if necessary to related tables. Draw relationships between the tables, noting if they are one-to-one or one-to-many. If they are many-to-many, then create linking tables.
Determine whether the tables are in First Normal Form. Are all fields atomic? Are there any repeating groups? Decompose if necessary to meet 1NF.
Determine whether the tables are in Second Normal Form. Does each table describe a single entity? Are all non-key columns fully dependent on the primary key? Put another way, does the primary key imply all of the other columns in each table? Decompose to meet 2NF. If the table has a composite primary key, then the decomposition should, in general, be guided by breaking the key apart and putting all columns pertaining to each component of the primary key in their own tables.
Determine if the tables are in Third Normal Form. Are there any computed columns? Are there any mutually dependent non-key columns? Remove computed columns. Eliminate mutual dependent columns by breaking out lookup tables.
Using the normalized tables from step 12, refine the relationships between the tables.
Create the tables using Microsoft Access (or whatever database program you are using). If using Microsoft Access, create the relationships between the tables using the Edit | Relationships command. Add sample data to the tables.
Create prototype queries, forms, and reports. While creating these objects, design deficiencies should become obvious. Refine the design as needed.
Bring the users back in. Have them evaluate your forms and reports. Are their needs met? If not, refine the design. Remember to re-normalize if necessary (steps 8-12).
Go back to the table design screen and add business rules.
Create the final forms, reports, and queries. Develop the application. Refine the design as necessary.
Have the users test the system. Refine the design as needed.
Deliver the final system.

This list doesn't cover every facet of the design process, but it's useful as a framework for the process.

Breaking the Rules: When to Denormalize

Sometimes it's necessary to break the rules of normalization and create a database that is deliberately less normal than it otherwise could be. You'll usually do this for performance reasons or because the users of the database demand it. While this won't get you any points with database design purists, ultimately you have to deliver a solution that satisfies your users. If you do break the rules, however, and decide to denormalize you database, it's important that you follow these guidelines:

Break the rules deliberately; have a good reason for denormalizing.
Be fully aware of the tradeoffs this decision entails.
Thoroughly document this decision.
Create the necessary application adjustments to avoid anomalies.

This last point is worth elaborating on. In most cases, when you denormalize, you will be required to create additional application code to avoid insertion, update, and deletion anomalies that a more normalized design would avoid. For example, if you decide to store a calculation in a table, you'll need to create extra event procedure code and attach it to the appropriate event properties of forms that are used to update the data on which the calculation is based. If you're considering denormalizing for performance reasons, don't always assume that the denormalized approach is the best. Instead, I suggest you first fully normalize the database (to Third Normal Form or higher) and then denormalize only if it becomes necessary for reasons of performance. If you're considering denormalizing because your users think they need it, investigate why. Often they will be concerned about simplifying data entry, which you can usually accomplish by basing forms on queries while keeping your base tables fully normalized. Here are several scenarios where you might choose to break the rules of normalization:

You decide to store an indexed computed column, Soundex, in tblCustomer to improve query performance, in violation of 3NF (because Soundex is dependent on LastName). The Soundex column contains the sound-alike code for the LastName column. It's an indexed column (with duplicates allowed) and is calculated using a user-defined function. If you wish to perform searches on the Soundex column with any but the smallest tables, you'll find a significant performance advantage to storing the Soundex column in the table and indexing this computed column. You'd likely use an event procedure attached to a form to perform the Soundex calculation and store the result in the Soundex column. To avoid update anomalies, you'll want to ensure that this column cannot be updated by the user and that it is updated every time LastName changes.
In order to improve report performance, you decide to create a column named TotalOrderCost that contains a sum of the cost of each order item in tblOrder. This violates 2NF because TotalOrderCost is dependent on the primary key of tblOrderDetail, not on tblOrder's primary key. TotalOrderCost is calculated on a form by summing the column TotalCost for each item. Since you often create reports that need to include the total order cost, but not the cost of individual items, you've broken 2NF to avoid having to join these two tables every time this report needs to be generated. As in the last example, you have to be careful to avoid update anomalies. Whenever a record in tblOrderDetail is inserted, updated, or deleted, you will need to update tblOrder, or the information stored there will be erroneous.
You decide to include a column, SalesPerson, in the tblInvoice table, even though SalesId is also included in tblInvoice. This violates 3NF because the two non-key columns are mutually dependent, but it significantly improves the performance of certain commonly run reports. Once again, this is done to avoid a join to the tblEmployee table, but introduces redundancies and adds the risk of update anomalies.

Summary

This article has covered the basics of database design in the context of Microsoft Access. The main concepts covered were:

The relational database model was created by E.F. Codd in 1969 and is founded on set theory and logic.
A database designed according to the relational model will be efficient, predictable, well performing, self-documenting and easy to modify.
Every table must have a primary key, which uniquely identifies rows in the table.
Foreign keys are columns used to reference a primary key in another table.
You can establish three kinds of relationships between tables in a relational database: one-to-one, one-to-many or many-to-many. Many-to-many relationships require a linking table.
Normalization is the process of simplifying the design of a database so that it achieves the optimum structure.
A well-designed database follows the Normal Forms.
The entity integrity rule forbids nulls in primary key columns.
The referential integrity rule says that the database must not contain any unmatched foreign key values.
Business rules are an important part of database integrity.
A well-designed database requires business insight, time, and experience.
Occasionally, you made need to denormalize for performance.

Database design is an important component of application design. If you take the time to design your databases properly, you'll be rewarded with a solid application foundation on which you can build the rest of your application.

source:http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx By Paul Litwin

Relational Database Table Design Standards and Principles

noreply@blogger.com (அசுரன் திராவிடன்) — Fri, 02 Apr 2010 18:25:00 +0000

TABLE NAMING STANDARDS

· The table name must clearly identify the one type of data the table contains.

Table names should usually be plural.

· Table names are the only database objects not prefixed.

· Only the first latter of each word in a table name should be capitalized.

· Functional table names (for many-to-many relationships) should be a combination of the two tables referenced. For example, SubprojectsDocuments indexes the many-to- many relationship between Subprojects and Documents.

· Lookup tables should be grouped together. They can be grouped at the end by starting the name with the letter “z”. Lookup table names may be singular.

· Developer’s tables begin with “zz”. (Example: zzChangeLog)

FIELD STANDARDS

· Field names should be descriptive and clearly understandable by someone who looks at them for the first time.

· Field names should contain letter, number, and underscore characters (“_”) only.

· The primary key field should always end with “ID”. If the field is not autonumber, all relationships to that field must have referential intergrity with cascade update.

· A field referencing a primary key field in another table should have exactly the same name as the field it is referencing.

· If multiple fields in a table reference the same primary key in another table, the field names should be followed by a suffix. (Example: BankID_from BankID_to)

· Abbreviations for field names should be avoided. An exception is when the table name contains or more words, the initials of each word may be used, for any field except the Primary Key field, and the description field. For example, if the table name is “FacilitatingPartners”, a field may be named “FPName”. The Primary Key would be “FaciliatingPartnerID”.

· Fields with Dari text have a name ending with “_dari”

· Composite keys should never be used. If necessary, use a composite index instead.

· Field names should be unique throughout the database.

NORMALIZATION PRINCIPLES

· A field is a fact about a particular subject (the field contain one piece of data).

· Fields in a database should be fully functionally dependent on the primary key.

· All non-key fields should be mutually independent. For example, a calculated field should not be stored. (Note: sometimes this rule must be violated for performance reasons.)

JOIN PROPERTIES

· Inner Join – contains matching records from both tables

· Left Join – contains all the records from the left table, and matching from the right

· Right Join – contains all the records from the right table, and matching from the left

JOIN TYPES

· One-to-one

· One-to-many

· Many-to-many – requires an intermediate table

noreply@blogger.com (அசுரன் திராவிடன்) — Wed, 24 Mar 2010 01:52:00 +0000

Using a Pivot Table

Problem
Support for a sequence of elements is often needed to solve various SQL problems. For example, given a range of dates, you may wish to generate one row for each date in the range. Or, you may wish to translate a series of values returned in separate rows into a series of values in separate columns of the same row. To implement such functionality, you can use a permanent table that stores a series of sequential numbers. Such a table is referred to as a Pivot table.

Many of the recipes in our book use a Pivot table, and, in all cases, the table's name is Pivot. This recipe shows you how to create that table.

Solution:
First, create the Pivot table. Next, create a table named Foo that will help you populate the Pivot table:

CREATE TABLE Pivot (i INT,PRIMARY KEY(i))

CREATE TABLE Foo(i CHAR(1))

The Foo table is a simple support table into which you should insert the following 10 rows:

INSERT INTO Foo VALUES('0')

INSERT INTO Foo VALUES('1')

INSERT INTO Foo VALUES('2')

INSERT INTO Foo VALUES('3')

INSERT INTO Foo VALUES('4')

INSERT INTO Foo VALUES('5')

INSERT INTO Foo VALUES('6')

INSERT INTO Foo VALUES('7')

INSERT INTO Foo VALUES('8')

INSERT INTO Foo VALUES('9')

Using the 10 rows in the Foo table, you can easily populate the Pivot table with 1,000 rows. To get 1,000 rows from 10 rows, join Foo to itself three times to create a Cartesian product:

INSERT INTO Pivot SELECT f1.i+f2.i+f3.i FROM Foo f1, Foo F2, Foo f3

If you list the rows of Pivot table, you'll see that it has the desired number of elements and that they will be numbered from 0 through 999.

You can generate more rows by increasing the number of joins. Join Foo four times, and you'll end up with 10,000 rows (10 * 10 * 10 * 10).

Discussion:
The Pivot table is often used to add a sequencing property to a query. Some form of Pivot table is found in many SQL-based systems, though it is often hidden from the user and used primarily within predefined queries and procedures.

You've seen how the number of table joins (of the Foo table) controls the number of rows that our INSERT statement generates for the Pivot table. The values from 0 through 999 are generated by concatenating strings. The digit values in Foo are character strings. Thus, when the plus (+) operator is used to concatenate them, we get results such as the following:

'0' + '0' + '0' = '000'

'0' + '0' + '1' = '001'
..

These results are inserted into the INTEGER column in the destination Pivot table. When you use an INSERT statement to insert strings into an INTEGER column, the database implicitly converts those strings into integers. The Cartesian product of the Foo instances ensures that all possible combinations are generated, and, therefore, that all possible values from 0 through 999 are generated.

It is worthwhile pointing out that this example uses rows from 0 to 999 and no negative numbers. You could easily generate negative numbers, if required, by repeating the INSERT statement with the "-" sign in front of the concatenated string and being a bit careful about the 0 row. There's no such thing as a -0, so you wouldn't want to insert the '000' row when generating negative Pivot numbers. If you did so, you'd end up with two 0 rows in your Pivot table. In our case, two 0 rows are not possible, because we define a primary key for our Pivot table.

The Pivot table is probably the most useful table in the SQL world. Once you get used to it, it is almost impossible to create a serious SQL application without it. As a demonstration, let us use the Pivot table to generate an ASCII chart quickly from the code 32 through 126:

SELECT i Ascii_Code, CHAR(i) Ascii_Char FROM Pivot WHERE i BETWEEN 32 AND 126
Ascii_Code    Ascii_Char
----------       ------------
32                  space

33                  !

34                  "

35                  #

36                 $

37                %

38               &
39                '

40               (

41               )

42              *

43             +

44              ,

45             -

46              .

47              /

48              0

49             1

50             2

51              3

What's great about the use of the Pivot table in this particular instance is that you generated rows of output without having an equal number of rows of input. Without the Pivot table, this is a difficult, if not impossible, task. Simply by specifying a range and then selecting Pivot rows based on that range, we were able to generate data that doesn't exist in any database table.

You must have enough Pivot table rows to accommodate the range that you specify. Had we used BETWEEN 32 AND 2000, our query would have failed, because our Pivot table has only 1,000 rows, not the 2,001 that would be required by such a large range.

As another example of the Pivot table's usefulness, we can use it easily to generate a calendar for the next seven days:
SELECT CONVERT(CHAR(10),DATEADD(d,i,CURRENT_TIMESTAMP), 121) date,DATENAME(dw,DATEADD(d,i,CURRENT_TIMESTAMP)) day FROM Pivot WHERE i BETWEEN 0 AND 6

date                             day
----------           -------------------
2001-11-05       Monday

2001-11-06      Tuesday

2001-11-07     Wednesday

2001-11-08     Thursday

2001-11-09     Friday

2001-11-10     Saturday

2001-11-11     Sunday

These two queries are just quick teasers, listed here to show you how a Pivot table can be used in SQL. As you'll see in other recipes, the Pivot table is often an indispensable tool for quick and efficient problem solving.

SQL Server Architecture

noreply@blogger.com (அசுரன் திராவிடன்) — Mon, 01 Mar 2010 14:15:00 +0000

Microsoft® SQL Server data is stored in databases. The data in a database is organized into the logical components visible to users. A database is also physically implemented as two or more files on disk.
When using a database, you work primarily with the logical components such as tables, views, procedures, and users. The physical implementation of files is largely transparent. Typically, only the database administrator needs to work with the physical implementation.
Each instance of SQL Server has four system databases (master, model, tempdb, and msdb) and one or more user databases. Some organizations have only one user database, containing all the data for their organization. Some organizations have different databases for each group in their organization, and sometimes a database used by a single application. For example, an organization could have one database for sales, one for payroll, one for a document management application, and so on. Sometimes an application uses only one database; other applications may access several databases.
It is not necessary to run multiple copies of the SQL Server database engine to allow multiple users to access the databases on a server. An instance of the SQL Server is capable of handling thousands of users working in multiple databases at the same time. Each instance of SQL Server makes all databases in the instance available to all users that connect to the instance, subject to the defined security permissions.
When connecting to an instance of SQL Server, your connection is associated with a particular database on the server. This database is called the current database. You are usually connected to a database defined as your default database by the system administrator.
SQL Server allows you to detach databases from an instance of SQL Server, then reattach them to another instance, or even attach the database back to the same instance. If you have a SQL Server database file, you can tell SQL Server when you connect to attach that database file with a specific database name.

MySQL 5 Storage Engines

noreply@blogger.com (அசுரன் திராவிடன்) — Wed, 10 Feb 2010 02:33:00 +0000

By Ian Gilfillan

New Storage Engines in MySQL 5

MySQL 5 offers a number of new storage engines (previously called table types). In addition to the default MyISAM storage engine, and the InnoDB, BDB, HEAP and MERGE storage engines, there are four new types: CSV, ARCHIVE, FEDERATED and EXAMPLE, as well as a new name for the HEAP storage engine. It is now called the MEMORY storage engine. None of the new types are available by default - you can check for sure with the SHOW ENGINES statement. Here is what is on my default version of MySQL Max:

mysql> SHOW ENGINES;

+------------+---------+-----------------
| Engine     | Support | Comment                                                    |
+------------+---------+-----------------
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     |
| HEAP       | YES     | Alias for MEMORY                                           |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  |
| MERGE      | YES     | Collection of identical MyISAM tables                      |
| MRG_MYISAM | YES     | Alias for MERGE                                            |
| ISAM       | NO      | Obsolete storage engine, now replaced by MyISAM            |
| MRG_ISAM   | NO      | Obsolete storage engine, now replaced by MERGE             |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys |
| INNOBASE   | YES     | Alias for INNODB                                           |
| BDB        | YES     | Supports transactions and page-level locking               |
| BERKELEYDB | YES     | Alias for BDB                                              |
| NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables             |
| NDB        | NO      | Alias for NDBCLUSTER                                       |
| EXAMPLE    | NO      | Example storage engine                                     |
| ARCHIVE    | NO      | Archive storage engine                                     |
| CSV        | NO      | CSV storage engine                                         |
+------------+---------+------------------------------

To add support for the missing storage engines, you currently need to build MySQL with certain options. It is likely though that there will be binary versions that include these storage engines by default at some point. Until then, there is no other way to enable them.
Changes in the MEMORY storage engine
You can read my 2003 article on the HEAP table type as a start, as most of the detail has not changed, and refer below for modifications in MySQL 5.
Previously, the HEAP storage engine only made use of hash indexes. These allow finding specific matches extremely quickly, but do not return any kind of range data. An index matches a record, but there is no ordering to allow it to return subsequent records. Only the complete index can be used, the concept of leftmost prefixing (using the left part of an index) does not apply. The MEMORY storage engine now permits BTREE indexes as well (the kind used by MyISAM tables by default).
To specify an index type, use the USING clause, as in the following examples:

CREATE TABLE memory_table (f1 INT, INDEX USING BTREE (f1)) ENGINE = MEMORY;
or
CREATE TABLE memory_table (f1 INT, INDEX USING HASH (f1)) ENGINE = MEMORY;

The HASH index is still the default, and will be the type of index created if you do not specify a particular kind.

Now support AUTO_INCREMENT
Now support INSERT DELAYED
Support indexes on columns that can contain NULL values
Never get converted to disk tables. (Temporary internal tables are automatically converted to disk table if they get too big, MEMORY tables never are. The max_heap_table_sizevariable (it hasn't yet changed its name to reflect the new storage engine name) places a limit on the memory utilization of MEMORY tables, and you can always place a MAX_ROWS limit as well, when creating the table.

The EXAMPLE storage engine

Added in MySQL 4.1.3 and only of interest to developers, the EXAMPLE storage engine does nothing, but is there to provide simple source code for developers to base new storage engines on. For those interested, the source code can be found in the sql/examples directory.
The FEDERATED storage engine
Added in MySQL 5.0.3, to make use of it you need to use the --with-federated-storage-engine option to configure when building MySQL. The FEDERATED storage engine allows you to access data from a table on another database server. That table can make use of any storage engine. Let's see it in action. First, CREATE a table on a remote server (you can do this on the same server for testing purposes, but doing so is fairly pointless otherwise).

CREATE TABLE myisam_table (f1 INT, PRIMARY KEY(f1))ENGINE=MYISAM;
Assuming that the default is set to create MyISAM tables (FEDERATED tables can access tables of any type), the above statement creates a definition file (.frm), an index file (.MYI) and a data file (.MYD). If you had created an InnoDB file, MySQL would create a definition (.frm) and index and data file (.idb). Now create the FEDERATED table on another server. The original table must always exist first:

CREATE TABLE federated_table (f1 INT, PRIMARY KEY(f1))ENGINE=FEDERATED

COMMENT='mysql://username:password@hostname.co.za:3306/dbname/myisam_table';

This creates a definition file (.frm), but the data and index files are that of the table on the remote server. The only unusual syntax is the COMMENT, which supplies the username, password (optional), port (optional), database and table name. This method is not particularly elegant, or secure, as the password is stored in clear text available to anyone who has access to the table data. However, in most cases it is likely that whoever has access to the FEDERATED table can also have access to the remote table, so this should not be too much of an issue. Bear in mind that this method of connecting will likely change in a future version.
There are some limitations on the use of FEDERATED tables. They are useful for easily accessing data on a different server, but fall short in many areas:

No Data Definition Language statements are permitted (such as DROP TABLE, ALTER TABLE)
They do not support transactions (since the remote server is contacted once, and the results returned to the local server)
Similarly, there is no way of being sure that the integrity of the local data is intact.
No prepared statements.

The CSV storage engine

Added in MySQL 4.1.4, tables of type CSV are actually just comma-delimited text files. This can be quite useful, and they exist to allow MySQL to interact easily with other applications that make use of CSV files, such as spreadsheets. They make no use of any sort of indexing. To enable this storage engine, use the --with-csv-storage-engine configure option when building MySQL.
Let's see how this works. You can import an existing CSV file. Assume you have a file containing firstname, surname and age, as follows:
"Jacob","Mbezela","42"

"Schabir","Field","29"

First, create the .frm definition file, as follows:

mysql>CREATE TABLE csv_names(firstname CHAR(30), surname CHAR(40), age INT) ENGINE = CSV;
The blank data file is also created. Since the CSV file is just a plain text file, you can copy an existing CSV file into the same location, and it will be viewable from the MySQL client, as follows:

mysql> SELECT * FROM csv_names;
+-----------+------------+-----+

| firstname | surname    | age |
+-----------+------------+-----+
| Jacob     | Mbezela    |  42 |
| Schabir   | Field      |  29 |
+-----------+------------+-----+

With no indexes, the SELECT is not at all efficient, and performs a complete table scan. Conversely, you can INSERT a record from the MySQL client:

mysql> INSERT INTO csv_names VALUES('Quinton','Baxter','75');

and view the change in the CSV file:

"Jacob","Mbezela","42"
"Schabir","Field","29"

"Quinton","Baxter","75"

The ARCHIVE storage engine

Added in MySQL 4.1.3, the archive storage engine lives up to its name by storing large amounts of data without taking up too much space. It too makes no use of any sort of indexing, and there are no means to repair the table should it become corrupted during a crash. To enable this storage engine, use the -with-archive-storage-engine configure option when building MySQL.

mysql> CREATE TABLE archive_names(firstname CHAR(30), surname CHAR(40), age INT) ENGINE = ARCHIVE;
This, as always, creates a .frm definition file, as well as .ARZ and .ARM data and metadata files.

Being an archive, you cannot DELETE, UPDATE or REPLACE records - you can only INSERT and SELECT. Again, with no indexes, the SELECT needs to perform a complete table scan. Although the records are compressed upon insertion, OPTIMIZE TABLE can compress the entire dataset even further. A .ARN file will temporarily appear when this occurs.

mysql> INSERT INTO archive_name VALUES('Quinton','Baxter','75');
mysql> SELECT * FROM archive_names;

+-----------+------------+-----+
| firstname | surname    | age |
+-----------+------------+-----+
| Quinton   | Baxter     |  75 |
+-----------+------------+-----+

Conclusion
The new storage engines, whilst tricky for most people to use since they require rebuilding MySQL, can be useful for those with special needs. After all, people have taken the time to write them! They are bound to be included in the binaries at some point, so if you are dying to try them, but rebuilding MySQL is not for you, you hopefully do not have to be patient for long. Even if you are able to use them now, keep an eye on the official documentation, as things are likely to change as they approach maturity. Good luck!

Optimizing MySQL: Queries and Indexes

noreply@blogger.com (அசுரன் திராவிடன்) — Wed, 10 Feb 2010 02:30:00 +0000

By Ian Gilfillan

You know the scene. The database is just too slow. Queries are queuing up, backlogs growing, users being refused connection. Management is ready to spend millions on "upgrading" to some other system, when the problem is really that MySQL is simply not being used properly. Badly defined or non-existent indexes are one of the primary reasons for poor performance, and fixing these can often lead to phenomenal improvements. Consider an extreme example:


CREATE TABLE employee (
   employee_number char(10) NOT NULL,
   firstname varchar(40),
   surname varchar(40),
   address text,
   tel_no varchar(25),
   salary int(11),
   overtime_rate int(10) NOT NULL
);

To find employee Fred Jone's salary(employee number 101832), you run: SELECT salary FROM employee WHERE employee_number = '101832';MySQL has no clue where to find this record. It doesn't even know that if it does find one matching, that there will not be another matching one, so it has to look through the entire table, potentially thousands of records, to find Fred's details.
An index is a separate file that is sorted, and contains only the field/s you're interested in sorting on. If you create an index on employee_number, MySQL can find the corresponding record very quickly (Indexes work in very similar ways to an index in a book. Imagine paging through a technical book (or more often, an scrambled pile of notes!) looking for the topic "Optimizing MySQL". An index saves you an immense amount of time!
Before we repair the table structure above, let me tell you about a most important little secret for anyone serious about optimizing their queries: EXPLAIN. EXPLAIN shows (explains!) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly, and what kind of join is being performed...
For example:


EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

So what are all these things?

table shows us which table the output is about (for when you join many tables in the query)
type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
possible_keys Shows which possible indexes apply to this table
key And which one is actually used
key_len give us the length of the key used. The shorter that better.
ref Tells us which column, or a constant, is used
rows Number of rows mysql believes it must examine to get the data
extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

Looks like our query is a shocker, the worst of the worst! There are no possible keys to use, so MySQL has to go through all the records (only 2 in this example, but imagine a really large table).
Now lets add the index we talked about earlier.
If we re-run the EXPLAIN, we get:

+----------+-------+---------------+---------+---------+-------+------+-------+
| table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| employee | const | PRIMARY       | PRIMARY |      10 | const |    1 |       |
+----------+-------+---------------+---------+---------+-------+------+-------+

The query above is a good one (it almost falls into the category of "couldn't be better"). The type of "join" (not really a join in the case of this simple query) is "const", which means that the table has only one matching row. The primary key is being used to find this particular record, and the number of rows MySQL thinks it needs to examine to find this record is 1. All of which means MySQL could have run this query thousands of times in the time it took you to read this little explanation.
============>>>>>>>>>>>>>>>>>>>

Some knowledge of how indexes work allows you to use them more efficiently. Firstly, note that when you update a table with an index, you have to update the index as well, so there is a performance price to pay. But unless your system runs many more inserts than selects and the inserts need to be quick, and not the selects, this is a price worth paying.
What about if you want to select on more than one criteria? (As you can see, it only makes sense to index those fields you use in the WHERE clause.) The query:
SELECT firstname FROM employee;
makes no use of an index at all. An index on firstname is useless. But,
SELECT firstname FROM employee WHERE surname="Madida";
would benefit from an index on surname.
Let's look at some more complex examples where EXPLAIN can help us improve the query. We want to find all the employees where half their overtime rate is less than $20. Knowing what you do, you correctly decide to add an index on overtime_rate, seeing as that's the column in the where clause.
ALTER TABLE employee ADD INDEX(overtime_rate);
Now let's run the query.
EXPLAIN SELECT firstname FROM employee WHERE overtime_rate/2<20;

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

Not good at all! Every single employee record is being read. Why is this? The answer lies in the "overtime_rate/2" part of the query. Every overtime_rate (and hence every record) has to be read in order to divide it by 2. So we should try and leave the indexed field alone, and not perform any calculations on it. How is this possible? This is where your school algebra comes to the rescue! You know that 'x/2 = y' is the same as 'x = y*2'.We can rewrite this query, by seeing if the overtime_rate is less than 20*2. Let's see what happens.
EXPLAIN SELECT firstname FROM employee WHERE overtime_rate<20*2;

+--------+-------+---------------+---------------+---------+------+------+----------+
|table   | type  | possible_keys | key           | key_len | ref  | rows |Extra     |
+--------+-------+---------------+---------------+---------+------+------+----------+
|employee| range | overtime_rate | overtime_rate |       4 | NULL |    1 |where used|
+--------+-------+---------------+---------------+---------+------+------+----------+

Much better! MySQL can perform the 20*2 calculation once, and then search the index for this constant. The principle here is to keep your indexed field standing alone in the comparison, so that MySQL can use it to search, and not have to perform calculations on it.
You may say that I was being unfair, and should have phrased the request as "where the overtime rate is less than 40", but users seem to have a knack of making a request in the worst way possible!
==========>>>>>>>>>>>>>>>>>>>>

Ordering by surname is a common requirement, so it would make sense to create an index on surname. But in this example our employee table consists of thousands of people from Swaziland, and with the surname "Dlamini". So we need to index on firstname as well. The good news is that MySQL uses leftmost prefixing, which means that a multi-field index A,B,C will also be used to search not only for a,b,c combinations, but also A,B as well as just A.
In our example, this means that an index of the type
ALTER TABLE employee ADD INDEX(surname,firstname);
is used for a queries such as
EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida';
as well as
EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida' and firstname="Mpho";
which both result in

+--------+------+-------------------+---------+---------+-------+------+-----------+
|table   | type | possible_keys     | key     | key_len | ref   | rows |Extra      |
+--------+------+-------------------+---------+---------+-------+------+-----------+
|employee| ref  | surname,surname_2 | surname |      41 | const |    1 |where used |
+--------+------+-------------------+---------+---------+-------+------+-----------+

However, the query
EXPLAIN SELECT overtime_rate FROM employee WHERE firstname='Mpho';
does not use an index, as firstname is not available from the left of the index, as shown below.

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    3 | where used |
+----------+------+---------------+------+---------+------+------+------------+

If you needed this kind of query, you would have to add a separate index on firstname.

The Query Optimizer, OPTIMIZE and ANALYZE

The magic inside MySQL that decides which keys, if any, to use to in the query, is called the query optimizer. It takes a quick glance at the index to see which indexes are the best to use. Compare it to searching for a CD by the artist "Savuka", called "Third World Child", where there are 2 indexes, one alphabetical by artist name, and the other by album name. At a glance, you see that there are 20000 unique artists, and 400000 unique albums, so you decide to search by artist. But if you knew that there were 50 Savuka albums, and that Third World child is the only album starting with "T", your search criteria would change. You can provide similar information for the Optimizer by running
ANALYZE TABLE tablename;
This stores the key distribution for the table (running ANALYZE is equivalent to running myisamchk -a or myismachk --analyze).
Many deletes and updates leave gaps in the table (especially when you're using varchar, or in particular text/blob fields). This means there are more unnecessary disk I/O's, as the head needs to skip over these gaps when reading. Running
OPTIMIZE TABLE tablename
solves this problem. Both of these statements should be run fairly frequently in any well looked after system.
Another factor that most people don't use when indexing is to take advantage of short indexes. You don't have to index on the entire field. Our surname and firstname fields are 40 characters each. That means the index we created above is 80 characters. Inserts to this table then also have to write an additional 80 characters, and selects have 80 character blocks to maneuvre around (disk I/O is the primary hardware bottleneck, but that's for another day!). Try reducing the size of your index - in the example above, rather use.
ALTER TABLE employee ADD INDEX(surname(20),firstname(20));
Now our updates write to an index half the size, and selects have a smaller index to search. Both will be faster (unless you make the indexes too short - imagine a book index, instead of giving the full word, only contained the first letter of the word!. You'd spend a lot of time looking up "semaphore" and "saxophone" when you actually wanted "SQL". Don't do the same to MySQL!
The same applies to the original field definitions. In these days of ample disk space, we don't often worry about space. But smaller usually means faster, so defining our surname and firstname fields as CHAR (255) would be a mistake if the biggest firstname is never more than 20 characters! You don't want to cut names off, but remember that you can ALTER the field later if conditions change, and you need to allow for more characters. I also suggest usingVARCHAR rather than CHAR (variable length characters rather than fixed length characters), even though many don't recommend this as they are more subject to fragmentation. I overcome this by using OPTIMIZE often.
============>>>>>>>>>>>>>>>>

Most systems need to be highly optimized for selects - take a news site which performs millions of queries per day, but where the data arrives in large batches of text files. So for parts of the day, inserts need to be optimal, without noticeably affecting the millions trying to access the data...
Assuming a nicely formatted '|' delimited text file that we want to insert into the table above, take this piece of PHP code:

if (!($fp = fopen("datafile.txt","r"))) {               // open the file for reading
  print "\nUnable to open datafile.txt for writing";    // display error
  exit();                                       // end the running of the program
}


while (!feof ($fp)) {                   // loop through the file line by line
  $sline = fgets($fp, 4096);            // put the data into the variable $sline
  $sline = chop($sline);                        // remove the newline
  list($eno,$fname,$sname,$telno,$salary) = split("|",$code);
                        // split the line on "|", populating the ind. variables
  $db->query("insert into employee(employee_number,firstname,surname,
tel_no, salary
   values($eno,'$fname','$sname','$tel_no', $salary)");
}                                               // end while loop

This would work, but would be very slow. The index buffer would be flushed after every insert. Until recently, MyISAM tables (The MySQL default) did not allow data to be inserted at the same time as being read. The new format does, but only if there are no deleted records present (highly unlikely in a heavily used system). So the entire table is locked for the duration of each insert. Fine on a low volume site, but when you're getting hundreds or thousands of queries per second, you'll soon notice the backlog!
There's a solution however - the best way to insert the data is to use MySQL's "LOAD DATA INFILE". This is much faster (20 times according to MySQL), and the only way some systems I've seen are still hanging in there!
The syntax is simple, and the code becomes a lot simpler too:

$db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");

LOAD DATA INFILE has defaults of:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
if you don't specify any of these clauses. And, just as with an ordinary insert, you need to specify a field list if the order of the fields is different, or, as in the example above, you're not inserting data for every field. Always specifying a field list is good practice for all queries anyway - if someone adds a field to the table at a later stage, you don't want to go back and have to fix all your previous INSERT and SELECT * statements.
If you can't get this to work properly, have a look at the format of your text file - every problem I've seen with LOAD DATA has been because of a corrupted text file. Every field in every row must be delimited correctly!
You may not always be inserting from a text file - perhaps your application needs to do many unrelated inserts continually. There are ways to make sure the mass of users selecting are not badly affected... The first is to use INSERT LOW PRIORITY. This waits until there are no more reads waiting to happen, waiting for the gap, and not pushing in as it were. Of course, if your database is a rush hour special, there may never be a gap, and the client performing the INSERT LOW PRIORITY may start to grow cobwebs! An alternative here is INSERT DELAYED. The client is immediately freed, and the insert put into a queue (with all the other INSERT DELAYED's still waiting for the queue to end). This means that there can be no meaningful information passed back to the client, (such as the auto_increment value), as the INSERT has not been processed when the client is freed. Also, be aware that a catastrophe such as an unexpected power failure here will result in the queued INSERT's being lost. For neither of these methods do you have any idea when the data will be inserted, if at all, so I suggest you use with caution.

Conclusion

It's not only getting the data in that needs to be quick - sometimes you need to get it out quickly too. (Say you've accidentally loaded yesterday's classified ads, for example). Don't do a:
DELETE FROM classifieds;
Rather, use:
TRUNCATE TABLE classifieds;
The difference here is that DELETE drops records one by one, and that can be 1 million one by one's too slow! Note that this does not apply before version 4.0 of MySQL. At time of writing, most of you will still be using 3.x versions (if you do a DELETE FROM tablename on a non-empty table, and get 0 records back as a result, you're running an earlier version. To fix this problem, MySQL made DELETE remove records one by one so as to return the number of records deleted, but TRUNCATE still did the quick delete. Also, earlier versions than 3.23.33 used TRUNCATE tablename, not TRUNCATE TABLE tablename)
This has only been a brief introduction to optimizing queries and indexes. Once you've mastered these tips, you'll want to look at your more complex joins that just never seem to perform quickly. The MySQL site has lots of useful information, and there are a few good books out there as well. But don't forget EXPLAIN! Often the best way is to try and rewrite the query in as many different ways as possible, and see which one runs more efficiently. You can learn a lot by trying to figure out why one alternative ran faster than the other. Good luck!
resource list:
www.mysql.com - complete documentation for MySQL

Strengths and Weakness of MySQL

noreply@blogger.com (அசுரன் திராவிடன்) — Fri, 02 Oct 2009 09:36:00 +0000

Strengths:

■■ Speed ■■ Reliability ■■ Low system resource requirements ■■ Scalability ■■ Platform diversity ■■ Support for a large number of host languages ■■ ODBC support ■■ Free or low-cost licensing ■■ Inexpensive commercial support ■■ Strong user community backing ■■ Availability of the source code

Weaknesses:

■■ Lack of certain SQL features ■■ Lack of thorough testing on certain platforms ■■ Difficulty of working with the source code

Prominent Users of MySQL

noreply@blogger.com (அசுரன் திராவிடன்) — Fri, 02 Oct 2009 09:21:00 +0000

MySQL has penetrated the enterprise in a way that perhaps would be odd for a typical proprietary application, but is very common for an open-source package. Developers brought it in through the back door, and sometimes by the time management found out, developers had a working solution based on MySQL with zero database licensing costs. This often won acceptance for MySQL at both the management and development levels. Some prominent users of MySQL have publicly released the fact that they are using MySQL and somewhat elaborated on their use, which allows us to talk about them in this book. Others are using it in production—frequently under heavy load—but treat this information as a trade secret. The organizations I mention in this section have made their use of MySQL public, so we can discuss a few details about their implementations. The companies listed here are the tip of the iceberg. MySQL AB collects user stories and publishes them at www.mysql.com/ press/user_stories/, so you might want to check there for more information.

Yahoo! Finance

Yahoo! Finance (finance.yahoo.com) uses MySQL to power a portion of the Web site. The database contains a total of 25GB, with the largest table containing over 274 million records and 8GB of data. The platform is x86 Linux and FreeBSD. The setup is replicated: one master and three slaves. The master is the most heavily loaded, and at peak times processes over 1,200 queries per second with the read/write ratio of 70/30.

NASA

NASA is using MySQL as a backend for the NASA Acquisition Internet Service (NAIS) site (nais.nasa.gov). This system has been reported to handle several thousand users and is receiving 300,000 hits per month. The database runs on Sparc Solaris. While the load and the database size is far below the top capacity of MySQL, NASA has been very pleased with the cost reduction and improved performance since it migrated from Oracle.

U.S. Census Bureau

The U.S. Census Bureau provides access to census information through three sites: www.fedstats.gov, www.mapstats.gov, and www.quickfacts.gov. These sites use MySQL as their backend database solution. The load on the sites is approximately 120,000 pages per day. Although the U.S. Census Bureau could have used Oracle for no additional cost (it has an Oracle site license), it chose MySQL for “its ease of installation, maintainability, configuration and speed,” according to Rachael LaPorte Talor, the Senior Web Technology Architect for FedStats.gov. The database runs on x86 Linux.

Texas Instruments

Texas Instruments uses MySQL to store regression test results for its semiconductor products. The database contains over 13 million records, filling up 5GB of data. Additionally, MySQL is used for a bug-tracking database that keeps track of 70 projects with 1,000 users. The platform is Sparc Solaris.

SS8 Networks

SS8 uses MySQL in its Local Number Portability (LPN) product for persistent storage of information pertaining to phone customers who have moved and switched their carriers. The supported capacity is up to 50 million records. MySQL was chosen for its performance, low resource requirements, and low licensing costs.

Moble.de

Moble.de runs an online car dealership with 315 million pageviews per month and a MySQL database containing records for 600,000 used vehicles. Additionally, its banner server delivers over 150 million impressions per month. It is using MySQL’s replication functionality and propagating its data from a master server to 50 slaves. All systems run x86 Linux. Moble.de initially tried to set up one of the “big names” as its backend, but had a hard time getting the replication to work. MySQL replication worked flawlessly with very little configuration effort, which greatly influenced its database choice.

What is MySQL?

noreply@blogger.com (அசுரன் திராவிடன்) — Sun, 02 Aug 2009 03:06:00 +0000

• MySQL is an SQL based relational database management system that runs on more than 20 platforms including Windows, Linux, OS/X, HP-UX and many more. • The owner and producer of MySQL is a Swedish company called MySQL AB. They provide services and training programs for MySQL users. The software’s official website http://www.mysql.com gives the latest information about the company and MySQL. • MySQL is declared the world’s most popular open source database by its founders: “It's used in more than 6 million installations ranging from large corporations to specialized embedded applications on every continent in the world. (Yes, even Antarctica!).” www.mysql.com • The reasons for its popularity are: 􀂃It is free and open source! Users can download the code for free and modify it according to their needs. 􀂃Its consistent fast performance. Speed is crucial in database driven applications since the time it takes for the user to see the result of a query is the main performance measure for the application. 􀂃High reliability. Every release of MySQL is heavily tested by users and developers all over the world since it is free of charge and open source. 􀂃Ease of use. The main interaction with the MySQL server is through SQL commands which are easy to master! 􀂃It is supported by PHP (more on this later). • A brief history of the software, its name and its logo from its founders at http://dev.mysql.com: “We started out with the intention of using mSQL to connect to our tables using our own fast low-level (ISAM) routines. However, after some testing, we came to the conclusion that mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL. This API was designed to allow third-party code that was written for use with mSQL to be ported easily for use with MySQL. The derivation of the name MySQL is not clear. Our base directory and a large number of our libraries and tools have had the prefix “my” for well over 10 years. However, co-founder Monty Widenius's daughter is also named My. Which of the two gave its name to MySQL is still a mystery, even for us. The name of the MySQL Dolphin (our logo) is “Sakila,” which was chosen by the founders of MySQL AB from a huge list of names suggested by users in our “Name the Dolphin” contest. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Swaziland, Africa. According to Ambrose, the feminine name Sakila has its roots in SiSwati, the local language of Swaziland. Sakila is also the name of a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.” • Useful Links and References: 􀂃http://www.mysql.com The official MySQL website. Latest news, manuals, downloads. 􀂃Manuals: http://dev.mysql.com/doc/ 􀂃Downloads: http://dev.mysql.com/downloads/ 􀂃http://forums.mysql.com/ You can search for and read about MySQL related topics or ask your own questions here. In particular, you can find the topics related to PHP & MySQL at http://forums.mysql.com/list.php?52 . 􀂃http://forums.devshed.com/f4/s.html Another popular forum on MySQL. 􀂃http://www.analysisandsolutions.com/code/mybasic An online tutorial on MySQL basics. 􀂃Books: 􀂃Beginning Databases with MySQL Author(s): Neil Matthew, Richard Stones

Components of the DBMS environment

noreply@blogger.com (அசுரன் திராவிடன்) — Sat, 01 Aug 2009 16:20:00 +0000

We can identify five major components in the DBMS environment: hardware, software, data, procedures, and people:
(1) Hardware: The computer system(s) that the DBMS and the application programs run on. This can range from a single PC, to a single mainframe, to a network of computers.
(2) Software: The DBMS software and the application programs, together with the operating system, including network software if the DBMS is being used over a network.
(3) Data: The data acts as a bridge between the hardware and software components and the human components. As we’ve already said, the database contains both the operational data and the meta-data (the ‘data about data’).
(4) Procedures: The instructions and rules that govern the design and use of the database. This may include instructions on how to log on to the DBMS, make backup copies of the database, and how to handle hardware or software failures.
(5) People :This includes the database designers, database administrators (DBAs), application programmers, and the end-users.

(Database) application programs

noreply@blogger.com (அசுரன் திராவிடன்) — Sat, 01 Aug 2009 16:12:00 +0000

Application program
A computer program that interacts with the database by issuing an appropriate request (typically an SQL statement) to the DBMS.

Users interact with the database through a number of application programs that are used to create and maintain the database and to generate information.These programs can be conventional batch applications or, more typically nowadays, they will be online applications. The application programs may be written in some programming language or in some higher-level fourth-generation language. .

The Database Management System (DBMS)

noreply@blogger.com (அசுரன் திராவிடன்) — Sat, 01 Aug 2009 16:01:00 +0000

DBMS
A software system that enables users to define, create, and maintain the database andalso provides controlled access to this database.

The DBMS is the software that interacts with the users, application programs,and the database. Among other things, the DBMS allows users to insert, update,delete, and retrieve data from the database. Having a central repository for alldata and data descriptions allows the DBMS to provide a general inquiry facilityto this data, called a query language. The provision of a query language (such as SQL) alleviates the problems with earlier systems where the user has to work with a fixed set of queries or where there is a proliferation of programs, giving major software management problems. We’ll discuss the typical functions and services of a DBMS in the next section The Structured Query Language (SQL – pronounced ‘S-Q-L’ or sometimes ‘See-Quel’) is the main query language for relational DBMSs, like Microsoft Access, Microsoft SQL Server, and Oracle.

The database

noreply@blogger.com (அசுரன் திராவிடன்) — Sat, 01 Aug 2009 15:50:00 +0000

Database
A shared collection of logically related data (and a description of this data), designed
to meet the information needs of an organization

Let’s examine the definition of a database in detail to understand this concept
fully. The database is a single, possibly large repository of data, which can be
used simultaneously by many departments and users. All data that is required
by these users is integrated with a minimum amount of duplication. And
importantly, the database is normally not owned by any one department or
user but is a shared corporate resource.
As well as holding the organization’s operational data, the database also
holds a description of this data. For this reason, a database is also defined as a
self-describing collection of integrated records. The description of the data, that is
the meta-data – the ‘data about data’ – is known as the system catalog or data
dictionary. It is the self-describing nature of a database that provides what’s
known as data independence. This means that if new data structures are added
to the database or existing structures in the database are modified then the
application programs that use the database are unaffected, provided they don’t
directly depend upon what has been modified. For example, if we add a new
column to a record or create a new table, existing applications are unaffected.
However, if we remove a column from a table that an application program uses,
then that application program is affected by this change and must be modified
accordingly.
The final term in the definition of a database that we should explain is ‘logically
related’. When we analyze the organization’s information needs, we
attempt to identify the important objects that need to be represented in the
database and the logical relationships between these objects. The methodology
we’ll present for database design will give you guidelines for identifying these
important objects and their logical relationships.

DATABASE DESIGN CONCEPTS AND SQL STANDARDS

Top 10 SQL Server DBA Interview Questions By Deanna Dicken

Introduction

What purpose does the model database serve?

How do you trace the traffic hitting a SQL Server?

What types of replication are supported in SQL Server?

Why would you use SQL Agent?

What happens on checkpoint?

What is DBCC?

How can you control the amount of free space in your index pages?

Why would you call Update Statistics?

What is a correlated sub-query?

What authentication modes does SQL Server support?

Conclusion

Database Fundamentals

Introduction to Relational Database Management System

Ten Common Database Design Mistakes

Poor design/planning

Ignoring Normalization

Poor naming standards

Lack of documentation

One table to hold all domain values

Using identity/guid columns as your only key

Not using SQL facilities to protect data integrity

Not using stored procedures to access data

Maintainability

Encapsulation

Security

Performance

Trying to code generic T-SQL objects

Lack of testing

Summary

Fundamentals of Relational Database Design

Overview

The Relational Model

Relational Database Design

Tables, Uniqueness and Keys

Foreign Keys and Domains

Relationships

One-to-One Relationships

One-to-Many Relationships

Many-to-Many Relationships

Normalization

Before First Normal Form: Relations

First Normal Form

First Normal Form (1NF) says that all column values must be atomic.

Second Normal Form

A table is said to be in Second Normal Form (2NF), if it is in 1NF and every non-key column is fully dependent on the (entire) primary key.

Third Normal Form

A table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent.

Higher Normal Forms

Integrity Rules

General Integrity Rules

Database-Specific Integrity Rules

A Practical Approach to Database Design

Breaking the Rules: When to Denormalize

Summary

source:http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx By Paul Litwin

Relational Database Table Design Standards and Principles

TABLE NAMING STANDARDS

FIELD STANDARDS

NORMALIZATION PRINCIPLES

JOIN PROPERTIES

SQL Server Architecture

MySQL 5 Storage Engines

New Storage Engines in MySQL 5

The EXAMPLE storage engine

The CSV storage engine

The ARCHIVE storage engine

Optimizing MySQL: Queries and Indexes

The Query Optimizer, OPTIMIZE and ANALYZE

Conclusion

Strengths and Weakness of MySQL

Strengths:

Weaknesses:

Prominent Users of MySQL

Yahoo! Finance

NASA

U.S. Census Bureau

Texas Instruments