Logical design using Entity Relationship Modelling (ERM) in a Database

In this post I will be discussing the Logical design using ERM in a Database.

Logical Database Design (ERM)

Below is a logical database design showing the logical relationship between a collector and their set of photos. Please note that the images in this post are not mine so I do not own any credit. They have been taken from a powerpoint my lecturer, Simon Perkins gave to me.collection_erd-01

As we can see this logical database design displays the relationship between a collector and their collection of photos. As we can see the ‘PHOTO‘ table includes different attributes that are linked to the same photo such as the photo’s id, file name, date stamp and so forth. But it is important to note that while this design allows a collector to have a variety of photos in their collection (1 or many photos) the design doesn’t allow the collector to create no more that one set of photos meaning that they will only ever be able to create a single set.

Physical database design

To overcome this this problem it is possible to test your design through using ‘dummy data’.

Screen Shot 2015-02-25 at 18.59.08

As we can see from the two tables above, we have split the collectors from their photos and put them in a separate table. This means if a request from PHP was pulled from a user, then the computer is able to identify the collector_id and their photo_id(s) and send the request back to the front end.

Now lets look at something a bit more complicated:collection_erd-02

If the left-hand side of the relationship line is solid then you read ‘must’, if it is broken you read ‘may’.

If the line (left/right hand side) has a crow’s foot then you read this as ‘many’, or if it is a single line you read this as ‘single’.

For this example you say: Each COLLECTOR must have one or more PHOTOs.

But it is important that as the digram can be read left to right it can also be read from right-to-left.

So like before, if the line is solid then you read ‘must’, if it is broken you read ‘may’.

And so goes for the crow’s foot method. So if there is a crows foot you read this as ‘many’, or if it is a single line you read this as ‘single’.

For this example you say: Each PHOTO must be owned by one and only one COLLECTORs

Below is an example of a logical database design that displays the logical relationship between a chef and their collection of recipes. We can brake this down into separate tables for the CHEF, RECIPE, PHOTO.

recipe_erd

This ERM describes the relationship between a chef and their collection of recipes, where the chef is able to create a variable number of recipes and where each recipe can have a variable number of photos.

Physical database design

Again, It is possible to test the design through using ‘dummy data’. Below we can see that each relationship has been put into their own table.

Screen Shot 2015-02-25 at 18.54.11

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s