DBA Solution to Table creation exercise

Following the exercise on SQL Table creation, we provide here an example of implementation.

Context

No context so far except for the modelling of the data.

Based on the given details we can picture some entities for our PDM (Physical Data Model) using JHipster JDL studio.

You could use whatever PDM tool that suits you, I enjoy working with JHipster JDL online tool from time to time.

entity Brand  {
  id Integer,
  name String
}

entity Car {
  id Integer,
  name String,
  creationDate Instant,
  brand Brand,
  color String,
  referringPrice BigDecimal
}

entity Person {
  id Integer,
  name String
}

entity Sale {
  id Integer,
  car Car,
  price BigDecimal,
  dateOfSale Integer,
  person Person
}

relationship OneToOne {
	Car{brand} to Brand,
	Sale{car} to Car,
  Sale{person} to Person
}

We simply represent the type of objects we are dealing with here with the information gathered into fields.

Here is the schema

Entities and relationship - Car sells

Now that you have a visual representation of the data, you can simply translate those entities into SQL tables.

I won’t be using a tool to generate the tables automatically but you could use SQL Power Architect instead of JDL (that will be more suitable actually).

SQL Tables

Following is the Postgresql representation of our entities :

CREATE TABLE brand (
  id serial PRIMARY KEY,
  name VARCHAR(25) NOT NULL UNIQUE
);

CREATE TABLE car (
  id serial PRIMARY KEY,
  name VARCHAR(25) NOT NULL UNIQUE,
  creation_date timestamp NOT NULL DEFAULT NOW(),
  brand_id integer NOT NULL REFERENCES brand ON DELETE CASCADE,
  color VARCHAR(10),
  referring_price decimal NOT NULL
);

CREATE TABLE person (
  id serial PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE sale (
  id serial PRIMARY KEY,
  car_id integer NOT NULL REFERENCES car ON DELETE CASCADE,
  price decimal NOT NULL,
  date_of_sale timestamp NOT NULL,
  person_id integer NOT NULL REFERENCES person ON DELETE CASCADE
);

If you are using Mysql or MariaDB instead, replace serial by integer AUTO_INCREMENT, the rest should be similar. Do not hesitate to ask if you struggle in applying it to your environment.

For Mysql & MariaDB, you should be using InnoDB instead of MyISAM to provide more control on top of your relationships.

Note that I tried to optimize a bit the size of the fields by filling in details around the used varchars – for instance a brand name shouldn’t be of a size over 25 characters…

To add more restrictions on the data, I’m using not null constraints and foreign keys relations between the tables – whenever you remove a key from the referenced table, it will automatically pass on referring tables (we use delete cascade for this purpose otherwise if you defined a field as not null, on delete it will trigger an error).

Using the above configuration we are conforming with the ACID (Atomicity Consistency Isolation Durability) principles.

Checkout this resource to lean more about those principles.

Furthermore whenever you insert a new sale or a new car, the date will be automatically set. We then avoid taking car of this using another application.

There is so much concept involved here, I advise you to follow the various  links provided to get a better understanding of why we implement such configuration.

Also I’m looking forward to reading your questions/comments anytime !

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want more information?

Related links will be displayed within articles for you to pick up another good spot to get more details about software development, deployment & monitoring.

Stay tuned by following us on Youtube.

%d bloggers like this: