sql - One to One relationship many tables -

i new using databases , trying design new database think need one-to-one relationship spread across many tables.

to demonstrate design, let's building schedule database example. start creating table person one-to-many relationship

create table person (     person_id serial not null,      name varchar,     primary key (person_id) ); 

next, create table of events contains many portion of person relationship

create table events (     event_id serial not null,      type varchar,     name varchar,     person_id integer,     time timestamp without time zone,     primary key (event_id),     foreign key(person_id) references person (person_id) ); 

now lets have 2 different types of events have different information them, meal , homework

create table meals (     event_id integer not null,      food varchar,     utensils varchar,     primary key (event_id),     foreign key(event_id) references events (event_id) );  create table homework (     event_id integer not null,      subject varchar,     completed boolean,     score float,     primary key (event_id),     foreign key(event_id) references events (event_id) ); 

now, reason i'm trying design database way because sometimes, may want display basic list of events each person, regardless of event is. instance, if initialize tables follows

insert person (name) values ('brad'); insert events (type, name, person_id, time) values ('meal', 'lunch', 1, '12/28/2016 12:00:00')     insert events (type, name, person_id, time) values ('meal', 'breakfast', 1, '12/28/2016 12:00:00'); insert meals (event_id, food, utensils) values (1, 'eggs', 'fork'); insert meals (event_id, food, utensils) values (2, 'turkey sandwich', 'hands'); insert events (type, name, person_id, time) values ('homework', 'final project', 1, '12/28/2016 18:00:00'); insert homework (event_id, subject, completed, score) values (3, 'math', 't', 0.93); 

then may want generate list of events brad

select (events.time, events.type, events.name) events  left join person on person.person_id = events.person_id  person.name = 'brad'; 

that easy, i'm confused is, if want see brad has eaten. think use 2 join statements between person , events , events , meals, if want walk through brads events , of information each event, (for instance, if event meal, tell me ate, if homework, tell me score got)?

overall have couple questions.

1. is database design or there else should consider? each of potential use cases above, plus couple more standard things need use database for.
2. how can determine table in more information given event in events table? couple of thoughts here -- store name of other table contains more information event in events table (ie, replace type column table column) think read somewhere bad idea.

a couple other notes, using postgresql database. actual database i'm building has lot more detailed information each table outside of i've shown here. trying give idea of i'm trying to. finally, building/accessing database using sqlalchemy's orm, if there's nifty trick can using relationships would useful know well.

if want details of each event, you're going have problem because tables hold event details have different types of columns. , don't want hardcoding various event details table names in code, after happens when want add or remove table, or change name? have update code everywhere!

so first of i'd you'll want view here. like:

create or replace view event_details     select * meals     union     select * homework; 

this allow select details of event types in single go, e.g.

select * event_details event_id in (     select event_id events person_id = (         select person_id         person         name = 'brad'     ) ) 

except doesn't work of course, because table structures different. you'll need find way represent data in uniform way; example, doing row_to_json on each record:

create or replace view event_details     select row_to_json(meals.*) details meals     union     select row_to_json(homework.*) details homework; 

and query:

select * event_details (details->>'event_id')::integer in (     select event_id events person_id = (         select person_id         person         name = 'brad'     ) ) 

gives you:

{"event_id":1,"food":"eggs","utensils":"fork"} {"event_id":2,"food":"turkey sandwich","utensils":"hands"} {"event_id":3,"subject":"math","completed":true,"score":0.93} 

and can parse json , want it. , when want add or remove or rename table, in view only.

now mind you, i'm not saying great (or only) way it. it's not clear me there's reason have separate table per event type, rather having 1 events table , putting type-specific data in jsonb field. make querying easier , faster, , if use jsonb type-specific data indexed well. think better design, based on examples you've shown.

wiki