Database schema design for MySQL, suggestions?

Question

So, have the chance to do my first "real" database, just thinking to myself what I should think about... any suggestions?

• Select DB (using MySQL)
• Select DB engine based on needs (using MySQL, know MyISAM vs InnoDB)
• CREATE TABLE documention for DB using
• Column definition (select right datatype)
• Model data to be normalized
• Referential Integrity: Primary Key (Natural or Surrogate); Composite Keys; Foreign Key

Which leads me to my main question, how do I know if the database is a success at fit enough?

Answer 1

As you are listing general questions that need to be answered in order to go from idea to working system I'll propose that it is very important to organize them into

1) logical design (get a clean model that represents the problem space you are trying to model)

2) physical design (which RDBMS and storage engine, exact data types, other practical and performance related decisions)

You are doing too much of mixing between the two. When you get a clean logical model and know the relationships between the entities you are modelling then the physical modelling will not be hard.

EDIT: There are many books that deal with the steps of logical data design, but normally you would try to:

1. define use cases and business requirements (things are pretty soft still, check the requirements for contradictions; this is done interviewing people who know business process well, which can degenerate to a discussion with yourself)
2. get a list of all the attributes and entities used across the system and define them (data dictionary)
3. determine the domain of the attributes (which, later at physical level can be accomplished as data type, check contraint or by referring to 'helper' table, but don't worry about this yet, just make sure that you define domains well)
4. draw ER/UML diagrams defining relationships - define tables in terms of primary keys, foreign keys and all other attributes (this time aim for a completeness); this step can be done using CAM and decent diagramming tools will spit out CREATE DATABASE scripts from diagrams
5. examine the model in search for denormalized data (should be normalized already, however when translating problem space into logical model it is possible to make mistakes and discover that you have redundancy or other anomalies)

A few of these steps need to go back and forth as you consider different ways of accomplishing certain tasks. For example including new attributes might make you go and analyze a new use case. Or a discovery of contradicting requirement might lead to a discovery of a whole new entity. Or discovering a redundancy might lead you to a discovery of undocumented process that exists (and justifies, or rather, explains percieved redundancy by redefining a seemingly duplicate attribute). Etc...

Answer 2

1. Model your data and normalise it before defining your columns. This needs to be your first task even before selecting a database and table tiypes as it will allow you to get clarity about the task you are modeling.

2. Select DB engine based on needs (using MySQL, know MyISAM vs InnoDB) The trade off is "query performance" v "transactions". In most cases innoDB performance is good enough and the benefits of transactions outweigh any downside.

3. Create table documentation is available on the MySQL website.

As Unreason says "When you get a clean logical model and know the relationships between the entities you are modelling then the physical modelling will not be hard".

Success can be measured in various ways. Money in your pocket, good performance on low priced hardware. Lots of happy comments from users ... Like Stackoverflow:)

Answer 3

1. Selecting a RDBMS is largely a matter of preference. You seem to be leaning towards MySQL already. That's okay, because MySQL is cheap and popular. However, you are left with not having an engine that can do transactions and full-text search at the same time (between MyISAM and InnoDB). Fulltext Search with InnoDB

2. (and 4) MyISAM vs InnoDB and datatypes

• MyISAM for: full-text search and table level locking
• InnoDB for: transactions, FKs, and row level locking (but no full-text search)
• Also, InnoDB will probably perform better with large number of rows because of row level locking versus table level locking

3. CREATE TABLE? I prefer to use a database IDE, like Toad for MySQL

5. (and 6) Review of DB normalization/PKs/FKs (You'll need to use InnoDB for FKs.)

7. You forgot indexes! Very important factor in a database.

• What is an index in MySQL?

• MySQL indexes - what are the best practises?

Yes MySQL is a good fit if you have the above requirements.

However, as I said, with MySQL/MyISAM/InnoDB, you don't have an engine that can do full-text search AND transactions/FKs. A simple option is to have a 2nd copy (in MyISAM) of the InnoDB tables that need full-text search capability. You can do this because you can mix the 2 engines in the same database. Or, maybe you don't even need full-text search because LIKE is sufficient for your application.

On the other hand, with SQL Server, you can have all the features, including full-text, transactions, and FKs all in one engine.

Yet another option, is to use a separate technology for indexed full-text searches. There's a plugin for MySQL:

• Sphinx Search Server
• Reference manual

Example:

• Using the Sphinx Search Engine with MySQL