This tutorial from Lynda.com proved to be far more accessible than the ERD found on folksonomy mainly because it looked at the whole of database design from the beginning while Frankies looked more at how to technically go about designing a database using a specific set of rules. While this is no doubt a useful way of doing it the full knowledge of database design is required I feel before jumping into the technical way of designing them. Just like it more useful to understand how code or programming works before attempting to create something using the code.
Below is my notes from the videos, My ERD will created using these and show in my next blog post.
- It’s ideal to be methodical and take you time.
- It’s best to start ultra-simple and work your way up.
First what’s the point?
To allow users to create and manage marketing personas and allow them to shared and contributed to by multiple different users and track the progress and usage of these personas for future development.
What do you already have?
Nothing except theoretical personas information. People, expertise, an existing database? Go over this information and fix any problems you find. I myself had literally nothing as my starting point except the list of information I wanted to be in the database.
What entities (tables) do you have? Generally use singular nouns for these but consistency is key. Picking the most useful or relevant words is the key here.
Users, Personas, Comments, Notes, Updates, Usage, statistics, case studies, Authorship, Industry, Role,.
Next is figure out the attributes of each entity, these will become the columns of each table, while they can be abstract or nonspecific when sketching out first drafts it is helpful to get specific early on. Whilst being granular as possible.
So for my purposes, we have to begin with
The entity’s, work out each ones attributes and then go on to decide the data type with in each attribute should be, as in intergar, character, number ect. Also if they are null or void null
One to one (Rare)
One to many (The vast majority)
Many to many (Requires resolving)
In many ways the database I am building can be compared to a typical learning database ie the customer to order relationship is similar to my user to Persona relationship. A single user can have many personas, but a persona may only be created by a single user (for now)
When and how to use foreign keys
You never change the “one” side you change the “many” side usually by adding the Pk to the many table where it know becomes a foreign key. This acts as the identifier to the table. The FK can be named the same as its primary key original or simply named to showed what it is, like the customer/order example it could be “customerId” or it could be “placed by”
So in my example each Persona (The many table) will have a FK from the user table, identifying who created which persona.
As I develop my ERD while going through the video I am starting to pick out various relation ships which before I thought would be contained within other tables, in particular separating our roles and Industry from personas.
Many to Many
This was where I realised that if I wished to have multiple users able to share and contribute on each persona I would need a linking or bridging table in order to have a many to many relationship for the contributing.
Having just reached the normalisation process in the tutorial I chose this to be a good break point for me to start with the sketching out and early conceptual modelling of the actual data base before I would move onto the more specific and technical areas.