Data blog — database design RSS

Data modeling and Monty Python

  When you approach new project, you are often tasked with design of the data structures that will support the application. The data structures should allow for the required functionality and they should follow some principles of the data modeling, like normalization, proper data types etc. How do you approach such tasks? Thinking in categories of tables is not always the best approach. Tables look like business entities, but not always. How do you model relationships between tables? Thomas Frisendal wrote a very interesting article about data modeling. One of the less clear stages in data model design is the architecture of entity relationships. Database professionals tend to either bypass the relationship names, or name them after the end point table names....

Continue reading

Enum data type in MySQL

While investigating features of MySQL database engine, I came across an interesting concept: the enum data type.   The enum data type allows for defining static data ranges that can be used as values of certain attributes. These ranges typically are quite small - for example marital status consists of 'Married', 'Single', 'Widowed', 'Divorced', 'N/A' . These five values can be encoded as an enum data type column. For example, we can define a table in the following way. create table Person ( FirstName varchar(50) not null, SurName varchar(50) not null, MaritalStatus enum ('Married', 'Single', 'Widowed', 'Divorced', 'N/A') not null ); Please note this is a very simplified code. Enum in MySQL has a certain feature - the values in rows...

Continue reading