The solution can be divided into two parts:
- Municipality support. This means adding a
Municipality
model (andmunicipalities
table) that thePackage
model belongs to (i.e. there is apackages.municipality_id
foreign key). I have retained the ability to have packages without a municipality so themunicipality_id
is allowed to be null. Also, the unique constraint for thePackage
model is no longer just on the name but instead on the combination of the name and the municipality (i.e. a composite index). This means that for example the Basic package can be present in the table once without a municipality and several times for different municipalities. - Improved price history. I have changed the price history feature so that now we store not only past prices in the
prices
table but also the current price. In addition, each row in theprices
table now has a price interval represented by theprice_valid_from
andprice_valid_to
columns. This makes it easier to query all prices for a package and makes it obvious how the different prices are laid out in time.
- I have not had time to create as many tests as I would have liked. I value tests very highly but I ran out of time.
- Due to time constraints I have not created a migration for the price history from the old to the new schema (i.e. populating the price_valid_from/price_valid_to columns and adding rows for current prices)
- The decision to keep the current prices without municipality may or may not be ideal depending on needs and requirements
- A more normalized data model where a package (such as
Basic
) is only represented once in the database and is then mapped to different municipalities in a separated table (i.e. join model) might have had some benefits (such as avoiding potential duplication of package metadata). On the other hand such normalization would probably have led to more complex queries (i.e. more joins). This decision also depends on if packages in different municipalities should be thought of as conceptually different entities or not (i.e. are they always or mostly the same etc.).