Mysql – Normalising a database for monitoring stations

database-designMySQLnormalization

I have data about monitoring stations. For now it is stored in Excel tabs. I have decided to try to make a database. It will be my first contact with databases. Here is an example how it looks now in Excel

Typ   Station name   Typ of area     Location     long    lat    Time/date/concent
pm      Kittsee        suburban     background
SO2   Oberschützen      rural        traffic
SO2     Kittsee        suburban     background
....
....

As you can see some stations occur two or more times. So I have decided to put them in another tab as

typ_id  typ
 1       pm
 2       SO2

then put station_id and typ_id together in the next tab

typ_id  station_id
 1         1
 2         1
 2         2

So my question is can I use this approach for location and area? So columns in my main table will look like this:

station_id typ_id station_name area_id location_id long lat time_date_conc

Best Answer

Yes, you can. However, some database implementations allow for enum or enumerable types, so if your type of station can come from just a small list, it may be better using an enum types sir, which has a similar effect to having a lookup table but runs much faster.
Also, you may not need a station I'd. Primary keys can be made of multiple columns and I don't know, but I would guess that there are no stations with the same name and type. If this is the case, you can make the primary key composed of both he'd columns!
Search for primary composite keys in your database implementation for how to do this, but in SQL it is done like so:

name VARCHAR(100) NOT NULL
type VARCHAR(10) NOT NULL
PRIMARY KEY (name, type)

Although bear in mind that your full create statement will have more information, and you may not want those lengths for your text.

Related Solutions

Database Design for Questions and Answers

We have an application at our workplace that does a similar thing. It works by having a table that contains a list of all possible questions like such:

CREATE TABLE QUESTIONS
(
   ID INT NOT NULL PRIMARY KEY,
   SUMMARY NVARCHAR(64) NOT NULL UNIQUE,
   DESCRIPTION NVARCHAR(255) NULL
);

Then you have an ANSWERS and a QUESTIONAIRES table defined using the same structure above. Once you have these two tables you then define a table to hold the list of question/answer possibilties as such:

CREATE TABLE QUESTION_ANSWERS
(
   ID INT NOT NULL PRIMARY KEY,
   QUESTION INT NOT NULL REFERENCES QUESTIONS(ID),
   ANSWER INT NOT NULL REFERENCES ANSWERS(ID)
);

Once you have these you can then create a table to contain the responses as such:

CREATE TABLE RESPONSES
(
   QUESTIONAIRE INT NOT NULL REFERENCES QUESTIONAIRES(ID),
   RESPONSE INT NOT NULL REFERENCES QUESTION_ANSWERS(ID)
);

This will give you maximum flexibility allowing you to add new questions and answers without having to change your database design frequently. It can get a bit complicated if you need to version the questions/answers but this should give you a good foothold to work from.

I hope this helps you.

Normalising shared entities and applying constraints

I can't point to anything authoritative, but if you think through the details of such an implementation, the potential down sides are evident.

Having a "shared" subsidiary table is problematic primarily because if two "parent" tables (say, Customer and Employee) both utilize the same Address table to store addresses, you can't use a foreign key constraint to ensure referential integrity between the Address records and the corresponding parent records.

Even if you forego the use of foreign key constraints, you'll have some conundrums when setting up your foreign key columns. You basically have three options, none of which are ideal:

Put an AddressID field in the Customer and Employee tables. The problems here are (1) you can't guarantee that an address won't be used more than once, and (2) if AddressID is auto-assigned, you can't store the address in the Customer/Employee table until the Address is created, which is backwards from how you'd probably want to insert the records (i.e., it makes Address the "parent" table).
Put CustomerID and EmployeeID columns in the Address table. Problems here include (1) again, you can't guarantee double-use of an address, (2) it wastes space since you're storing a NULL in one column or the other, and (3) it doesn't scale well as you find more entities that need addresses.
Collapse CustomerID/EmployeeID/etc. into a single column ParentID and another column ParentTypeID that distinguishes Customers from Employees. This scales better than (2) above, but has its own issues, such as the need to assign parent tables "magic numbers" for the ParentTypeID.

I won't say there aren't places where such approaches aren't pragmatic, but you do have to consider whether it's really that big a deal to have similar columns in multiple tables, and if trying to move them to a common table really buys you anything for the increased complexity in inserts, updates, joins, etc. IMHO, it's usually better each table to have clear constraints in how they relate to other tables, and for the "parent" tables to represent the core business objects being created (customers, employees, etc.), not subsidiary complex types that happen to be common to several other tables.

Best Answer

Related Solutions

Database Design for Questions and Answers

Normalising shared entities and applying constraints

Related Question