Help with a db schema referencing multiple historical events

Hello,

I'm having trouble setting up a schema for tuples referencing multiple historical events. I'd love a bit of help.

Say I have a list of cities in a dangerous area, like eastern Europe. Many of them will have been ruled by more than nation over the last few centuries. So in this example I have three cities:

city A: ruled by X since 1500 CE
city B: ruled by X from 1500-1700, by Y from 1700-2000
city C: ruled by Y from 1500-1600, by Z from 1600-1900, by X from 1900-2000

I would like to write a query like this (pseudo-code):
SELECT city, ruler WHERE year BETWEEN 1600 AND 1800.

How would I go about writing a schema to structure this type of data? Thanks a lot.

CREATE TABLE city_history
( city ... NOT NULL
, ruler ... NOT NULL
, year_from ... NOT NULL
, year_to ... NULL
, PRIMARY KEY ( city, ruler, year_from )
);

Worked great! Thanks a lot for the help.

Quick side question: is dash vs. underline an issue for MySQL? In other words, would MySQL have a problem with "x-coord" instead of "x_coord"? Thanks again.

yes

use underline

dash will be interpreted as subtraction

Thanks again.