问题描述:

In developing a historical and hierarchical SQL table, is it better to use start and stop date fields or a single date field for every date? There are pros and cons for each, but I'm looking for evidence as to which is more optimized, elegant, and takes into account corner cases most effectively.

Example -

-- Start/Stop

create table roster (

id bigint identity primary key,

start_date date not null,

stop_date date not null,

employee_id int not null references employee (id),

supervisor_id int not null references employee (id),

unique(start_date, stop_date, employee_id)

)

-- Single Date

create table roster (

id bigint identity primary key,

row_date date not null,

employee_id int not null references employee (id),

supervisor_id int not null references employee (id),

unique(row_date, employee_id)

)

相关阅读:
Top