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.
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’.
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.
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.
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.