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