vendredi 1 septembre 2017

Database Design approach with many 'chained' Tables

I want to create a database to store the fictional 'universe creation attempts'. My first idea was to break down the structure and I ended up with these tables. But when I viewed the tables as a diagram it suddenly looked like a bad approach :(. Now I want to ask for guidance if my approach is correct or if there is a better design pattern to create the structure I intend to create.

I was searching for information but I didnt get far with the term 'chained' or the infomation I found didnt really help me in finding an answer. Maybe I am using the wrong term for my search.

DROP TABLE IF EXISTS galactic_department CASCADE;
CREATE TABLE galactic_department (
    id serial UNIQUE PRIMARY KEY,
    code text
);

DROP TABLE IF EXISTS researcher CASCADE;
CREATE TABLE researcher (
    id serial UNIQUE PRIMARY KEY,
    galactic_department_id bigint REFERENCES galactic_department (id),
    code text,
    galactic_name text,
    galactic_phone text,
    galactic_email text
);

DROP TABLE IF EXISTS experiment CASCADE;
CREATE TABLE experiment (
    id serial UNIQUE PRIMARY KEY,
    creation_attampt_id text,
    conditions text,
    creation_success boolean,
    researcher_id bigint REFERENCES researcher (id),
    universe_id bigint REFERENCES universe (id)
);

DROP TABLE IF EXISTS universe CASCADE;
CREATE TABLE universe (
    id serial UNIQUE PRIMARY KEY,
    name text
);

DROP TABLE IF EXISTS cluster CASCADE;
CREATE TABLE cluster (
    id serial UNIQUE PRIMARY KEY,
    universe_id bigint REFERENCES universe (id),
    name text
);

DROP TABLE IF EXISTS galaxy CASCADE;
CREATE TABLE galaxy (
    id serial UNIQUE PRIMARY KEY,
    cluster_id bigint REFERENCES cluster (id),
    name text
);

DROP TABLE IF EXISTS star CASCADE;
CREATE TABLE star (
    id serial UNIQUE PRIMARY KEY,
    galaxy_id bigint REFERENCES galaxy (id),
    name text
);

DROP TABLE IF EXISTS planet CASCADE;
CREATE TABLE planet (
    id serial UNIQUE PRIMARY KEY,
    star_id bigint REFERENCES star (id),
    name text
);

DROP TABLE IF EXISTS planet_moon CASCADE;
CREATE TABLE planet_moon (
    id serial UNIQUE PRIMARY KEY,
    planet_id bigint REFERENCES planet (id),
    name text
);

DROP TABLE IF EXISTS lifeform CASCADE;
CREATE TABLE lifeform (
    id serial UNIQUE PRIMARY KEY,
    planet_id bigint REFERENCES planet (id),
    name text
);

DROP TABLE IF EXISTS lifeform_water CASCADE;
CREATE TABLE lifeform_water (
    id serial UNIQUE PRIMARY KEY,
    lifeform_id bigint REFERENCES lifeform (id),
    fin_count text
);

DROP TABLE IF EXISTS lifeform_ground CASCADE;
CREATE TABLE lifeform_ground (
    id serial UNIQUE PRIMARY KEY,
    lifeform_id bigint REFERENCES lifeform (id),
    leg_count text
);

DROP TABLE IF EXISTS lifeform_air CASCADE;
CREATE TABLE lifeform_air (
    id serial UNIQUE PRIMARY KEY,
    lifeform_id bigint REFERENCES lifeform (id),
    wing_count text
);

And a picture of the diagram. Database Model

Aucun commentaire:

Enregistrer un commentaire