Mysql

Before we can design a database, we have to consider the principle components underlying a database and how it is used. After all, a database is built with a user in mind.

A database is a collection of persistent data stored in a computer system. A user does not need to know how the data is stored. A user needs to be able to access the information that is required for a purpose. Data stored in this way can be thought of as an Information System.

An information system is a computer system whose primary purpose is to support the information requirements of its users. Relational databases can be viewed as data stored as tables.

Information or Data

Information is any kind of knowledge that is exchangeable amongst people, about things, facts, concepts etc., in some context.

In order to exchange information efficiently there is a requirement that individuals operate to a common Domain of Discourse (what is being talked about and its context) using an agreed language (and I don't mean English or French for example).

The above relates to sentences between people. For the purpose of databases, the main way of exchanging information is via a form. We therefore need a form language (construction rules).

Data is a representation of information - or - information is interpreted data.

If data is viewed out of context or without context, the information may not be correctly understood. Data has to be understood for it to make any kind of sense.

The computer system cannot provide the context. This is a human perception. The semantics or meaning.

A relational database has to be developed as a set of coordinated tables designed to satisfy specific requirements. There are different ways to achieve this with many methods, (methodologies) specifying a series of tasks. These tasks mirror the general principles of any computer system development.

There is no strict sequence to the following principles. What is certain is the fact that the process will become iterative. Questions will be raised at every stage. Answers will be found and these answers in themselves will raise more questions. Developers cannot hope to fully understand a users requirements from the outset. Different users will require different information from the data. The customer wants to know whats on the shelf, the buyer wants to know what needs to be on the shelf:

Establishing requirements - involves consultation with, and agreement among, users as to what persistent data they want, expressed as a statement of data requirements. A statement of the persistent data that needs to be maintained in order to satisfy the needs of the variety of users in the organisation.

Data Analysis - starts with the statement of data requirements to produce a conceptual data model - a formal representation of what data the database should contain, in terms independent of how it may be realised. The conceptual data model is used in database design activities. It is about the shared, conceptual representation of what is communicated between people during database development - the data in a database.

Database Design - takes the conceptual data model to produce a specification of a logical schema. The specification of all the tables in the database and their constraints. Choices are made as to the most appropriate tables.

Implementation - is the construction of a database according to the specification of the logical schema, which requires a specification of the storage schema. For relational databases the storage schema will be limited to choosing indexes to satisfy performance requirements.

More on the conceptual model

Entity - represents a thing that has meaning in a given context and about which there is a need to record data. A conceptual data model is not about an individual entity - it is about entity types, and will cover the common properties of a collection of entities.

Where there are several entities of the same type - each one is seen as an instance of the entity with its own unique identifier attribute. The entity type may also have other attributes.

Attribute - is a component of an entity type that represemts a single property of entities of that type. They should be named accordingly e.g. ProductId for a product.

Relationship - is an association between entities that has meaning in a given context and needs to be recorded.

The degree of a relationship can be:

* one to one
* one to many
* many to many
A participation condition can be optional if entities of that type do not need to be involved in the occurrence.

A participation condition can be mandatory if entities of that type must be involved in the occurrence. When the participation condition is mandatory it is a constraint.

Constraint - many aspects of constraints can be represented in a diagram. Others are simply written into the conceptual model.

Assumptions - any aspect of the model that may not be otherwise explicitly represented.

The above has barely been an introduction. Database design is a vast subject and I am no expert. While this may not be a particularly useful tutorial in itself, I hope it has provided an insight of what is to come when we look at some of the practicalities of the above. Until the next time. When I hope to move into the murky world of relational theory involving relations, tuples, cardinality and more.

About Table Designs

For novice MySQL developers designing the proper table structures for your first few tables isn't so obvious. MySQL Table Designs are examples of common but sometimes intricate table structures that are integrated into websites. Some table structures may focus on their relationships with other tables while others may introduce with fields that some beginners often overlook.

Standardized Naming

It is also important to note the standardized method in which the fields and tables are named throughout this section. While I've used a common standard of naming my fields and tables throughout these structures, you may choose a different standard or make your own as you go along. There are no 'right' ways when designing your naming standard, but there are mistakes you can avoid if you put some fore-thought into your naming standard. The standard I've implemented is what I commonly use throughout my applications and they are relatively easy to understand.

How to Plan Your Database

Planning your database should be a much higher priority then creating your database. If you take your time and plan accordingly, you can build a database that can accomidate growth. However, if you create your database without putting enough time into designing the architecture, you could have to make major changes in the future that will effect both your database and your code.

I strongly believe that every database design should start out on paper or in your text editor. Something that allows you to make quick changes if anything should come up. For the most efficient database design, you should design your entire database before you ever start your code. Bottom line... take your time and design. Always good to end on a corny rhyme. =)

The SELECT statement retrieves the information contained in your databases. You can also ask MySQL to show you information about your databases, such as database and table names or information about the columns or indexes in a table. This section discusses the SHOW and DESCRIBE statements, which provide the following types of information:

SHOW DATABASES;
SHOW TABLES [FROM db_name];
SHOW CREATE TABLE table_name;
DESCRIBE table_name;

Listing Database or Table Names

To determine the databases or tables that exist on your server, use the SHOW statement. SHOW is a versatile statement that has several variations for displaying many types of information.

SHOW DATABASES displays a list of the databases that your server manages: