Logical Data Model

Periodically I go through minor obsessions - these days it seams it's building Logical Data Models.

Why do I bother?

Well, firstly because I'm a lot better understanding visual stuff.

Secondly, because if you're not the one building the database you need to 'hand over' the data in some standard form.

So, here it is ready- a diagram of all data items Smashingtrips needs to manage ( well, a part of it, just to get an idea about how it looks).

 

And here it is, a short guide on how to build a Logical Data Model (assuming you know some basic stuff about data modelling).

1. Start with the High Level Business Requirements and underline the nouns.

'I'd like to be able to manage all my days out in one central place. I usually spend a day out either walking on the hills or in some urban place ...erm... walking. '

(I do other stuff as well and count it as a day out, however I'm not sure I want to record it or even remember some of them days, therefore those will be out of the scope of this exercise)

Back on track: for each Day Out, I'd like to be able to record detail information about the Route, Locations, Hazards, Places of interest.

I also bought a Garmin GPS, I'll record some of the routes as gpx routes and I'd like to be able to publish them for download.

The route will be described by an image map, and the walk can be already publish in a book in which case I'd like to be able to record the book name and a link to a shop selling it.

I want to be able to record a story for each day out and in each story to be able to include photos.

Each day out, based on the type, should be associated with a location: a city/ place or a region/ sub-region/ country. The website should maintain a list of such places in a nice hierarchy.'

2. Entities

Well, we've got them: all the underlined nouns above are your Entities, the categories for the data items. So go ahead and create a box for each.

3. Relationships

Start thinking of how the Entities relate together : how many instances of one Entity  will be involved in a relationship with another Entity?

The Day Out (Route) can have hazards described by how many Hazards?

These are the 'multiplicities' in your model and your choices are : 'one' (or an exact number), 'zero or many represented by  0..*', 'one or many represented by1..*'.

Some Dys Ot will have zero hazards, some might have many hazards, so the choice here is 'zero or many'. Easy.

4. Populate the Data Items/ atributes in the Entities as you go along.

5. Now, very important, let's address the subject of Keys.

For each Entity, find a data item (or a combination of Dagta Items) that will uniquely identify each Entity instance.

That is your Primary Key.

For each relationship, you'll need to copy the Primary Key into the linked Entities, in order to be able to navigate through the data model.

See the 'DayOut Id' in the DayOut Hazard Entity? This is what I'm talking about.

Have a look again at the model - it should make a bit more sense now.

6. Why the two colours for my entities boxes?

Well - some of the data is transactional data that will be changing dynamically (because I'll keep adding Days out content ), some of the data is Terminology data (or Lookup lists).

Lists of standard hazards, lists of standard types of places of interests, lists of countries etc.

My terminology data here is in green - useful to make the separation especially in real life, where some smart data designer might decide to build a reference/ terminology data service for anybody interested to use it at the Enterprise level.

The diagram above is done in Enterprise Architect, as a class model - EA is my tool of choice because of various positive experiences, way too many to mention.

Now that we have the data sorted, we'll see some screens hopefully.