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

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:

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.