Object oriented data modeling - Part 2

From javascript objects to database tables

Now this article is not about database design so I won’t go into details about how to normalize a relational database. But in case of splitting up the object like I have done in the last chapter I will just briefly add what’s necessary for the data to relate to each other.

Relations between database tables

As mentioned earlier each entry in the address book can have multiple email addresses and multiple phone numbers. That means you have to have some sort of id in each object so the data from the address book entry can relate to the email or phone number. That means the address book needs an id. That ID will then be referenced in the email or phone table.

const RelationalAddrBookEntries = [{
    id: 1, // <<<< This value will be added by the database system
    name: "John Doe",
    address: "No Way Street",
    postalCode: 75462,
    city: "Paris, Texas",
    country: "United States",
  }]

The value of the Id will be added by the database system. Every entry will have an unique ID. That means the email or phone number table will need a place to store that ID so when we require all the emails for an address book entry we can get that by looking at a given addrBookEntryId in the email table. The same when it comes to the phone numbers. So now the email object and phone object from before will need to be updated with a addrBookEntryId. The objects now look like this:

const relationalEmailAddresses = [{
    id: 1, // This value will be added by the database system
    addrBookEntryId: 1,  
    email: "email01@example.com",
  },{
    id: 2, // This value will be added by the database system
    addrBookEntryId: 1,
    email: "email02@example.com",
  }];

const relationalPhonNumbers = [{
    id: 1, // This value will be added by the database system
    addrBookEntryId: 1,
    phone: 12345678,
  },{
    id: 2, // This value will be added by the database system
    addrBookEntryId: 1,
    phone: 87654321,
  }];

Designing the database.

Now I can add and remove key value pairs or split up my objects into new objects until I'm satisfied according to my data design ideas. After I have finished designing my objects I now have a blueprint for how to design my database. Here is my approach to the database tables.

The address book entry

My object looks like this:

const RelationalAddrBookEntries = [{
    id: 1, // This value will be added by the database system
    name: "John Doe",
    address: "No Way Street",
    postalCode: 75462,
    city: "Paris, Texas",
    country: "United States",
  }]

Now I know how to model my database table. Because I have added some dummy data to my objects I know what datatype each column in the database will be. The database I use in this example is PostgreSQL. There are different ways to write the SQL scripts depending on what database system is used. In PostgreSQL auto increment of the ID is achieved by adding SERIAL to the ID column. In databases like MySQL or MariaDb I can add auto increment by adding AUTO_INCREMENT instead of SERIAL Now I can model the rest of the table. I won’t go into details about database design in this article. Just to show how I use the javascript object to create the database I will mention a few things about database table design. Each column in a database table needs at least a NAME and a DATA TYPE value. If I look at the javascript object I created earlier I know the names of the columns by its keys. There needs to be a column for Name, address, postalCode, city, country in the database table. When it comes to data type I have to be more aware. Looking at the dummy data I can see the object is mostly made out of strings and numbers. The postal code here are just integers so the postal code column could have INT as a datatype. But in some cases a postal code might contain letters like they do in the United Kingdom.²Here I can’t use INT as it will create an error or not save the data correctly. So to make the system more flexible I have to be aware of the many possibilities of each column in the database. Another thing I have to think about is the size of the data type meaning how much of the given data type the column can hold. That’s done by adding parentheses after the data type with a number. The number varies according to the data type. For example the data type varchar can hold up to 255 characters. If that is not enough I have to choose another data type that can hold more characters like TEXT, that have unlimited length. That could be used to save data from a HTML textarea where I can’t predict how much text is needed to be saved. Not all data types accept a size parameter so I always check the documentation if I’m in doubt.³

CREATE TABLE
    IF NOT EXISTS ADDRESS_BOOK (
        id SERIAL PRIMARY KEY, -- SERIAL is the Auto Increment of Postgresql
        name VARCHAR(20),
        address VARCHAR(255),
        postalCode INT,
        city VARCHAR(255),
        country VARCHAR(255)
    );

CREATE TABLE
    IF NOT EXISTS ADDR_BOOK_EMAILS (
        id SERIAL PRIMARY KEY, -- SERIAL is the Auto Increment of Postgresql
        addrBookEntryId INT,
        email VARCHAR(255)
    );

CREATE TABLE
    IF NOT EXISTS ADDR_BOOK_PHONES (
        id SERIAL PRIMARY KEY, -- SERIAL is the Auto Increment of Postgresql
        addrBookEntryId INT,
        phone_numbers VARCHAR(255)
    );

With these scripts I can create the database. Afterwards I can create other SQL scripts to insert, update or delete records.

Foot notes

Read more about postal codes here: https://www.geoapify.com/postcode-formats-around-the-world/ ⤻

List of PostreSQL data types: https://www.postgresql.org/docs/current/datatype.html ⤻

< Object oriented data modeling - Part 1

Using javascript objects for data modeling.

Table of Contents

Object oriented data modeling - Part 3 >

Creating the API.