Обсуждение: Design of a database table

On 07/30/2018 09:37 AM, hmidi slim wrote:
> I'm trying to design a database table. First of all there are two 
> alternatives:
> 1-) Divide the table into two tables and make a join.
> 2-) Design a single table.
>
> 1rst alternative:
> Create table data_periods(
> id serial primary key not null,
> period daterange,
> project_id integer
> )
>
> create table data_periods_info(
> id serial primary key not null,
> data_periods_id integer,
> data_sub_periods daterange,
> stock1 integer,
> stock2 integer,
> CONSTRAINT data_periods_allotment_id_fkey FOREIGN KEY (data_periods_id)
>         REFERENCES data_periods (id) MATCH SIMPLE
>         ON UPDATE NO ACTION
>         ON DELETE NO ACTION
> )

Are you absolutely 100% positive that there will NEVER be more than two 
stock numbers?  (People say "yes" to this kind of question all the time and 
then discover that they need more stock numbers when the business changes.)

-- 
Angular momentum makes the world go 'round.

On 07/30/2018 07:37 AM, hmidi slim wrote:
> I'm trying to design a database table. First of all there are two 
> alternatives:
> 1-) Divide the table into two tables and make a join.
> 2-) Design a single table.
> 
> 1rst alternative:
> Create table data_periods(
> id serial primary key not null,
> period daterange,
> project_id integer
> )
> 
> create table data_periods_info(
> id serial primary key not null,
> data_periods_id integer,
> data_sub_periods daterange,
> stock1 integer,
> stock2 integer,
> CONSTRAINT data_periods_allotment_id_fkey FOREIGN KEY (data_periods_id)
>          REFERENCES data_periods (id) MATCH SIMPLE
>          ON UPDATE NO ACTION
>          ON DELETE NO ACTION
> )
> 
> The table data_periods contains 1M rows and data_periods_info 5M rows.
> I added an index to the table data_periods_info for the column 
> data_periods_id
> I execute this query:
> select
> data_periods.id <http://data_periods.id>,
> data_sub_periods,
> project_id,
> stock1,
> stock2
> from data_periods
> inner join data_periods_info on data_periods_info.data_periods_id = 
> data_periods.id <http://data_periods.id>
> where data_periods.period && '[2018-07-28, 2018-08-02]'::daterange
> and data_sub_periods  && '[2018-07-28, 2018-08-02]'::daterange
> 
> I got an execution time of : 1s 300ms
> 
> 
> 2nd alternative:
> create table data_periods_second(
> id serial primary key not null,
> data_sub_periods daterange,
> project_id integer,
> stock1 integer,
> stock2 integer)
> 
> I run this query;
> select * from data_periods_second
> where data_sub_periods && '[2018-07-28, 2018-08-02]'::daterange
> 
> I got such a execution time : 1s
> 
> 
> Is it normal to get an execution time when using join relation greatest 
> than the execution time of a table contains million of rows and many 
> columns?

Not surprising given that you are searching for a date range in two 
tables in the join versus only one in the other case. That fact that you 
are using the same range end points for period in data_periods and 
data_sub_periods in data_periods_info, to me, points to a design flaw. 
If period and data_sub_periods are the same why separate and repeat 
them? Also when asking for input on query planning/outcomes running 
EXPLAIN ANALYZE on the queries and posting the results here will help 
arrive at answer.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/30/2018 11:40 PM, hmidi slim wrote:
> Actually, the data_periods contains a complete range such as 
> [2018-09-01,2018-09-30] and data_sub_periods contains sub periods 
> contained in this period like:
> [2018-09-05, 2018-09-07]
> [2018-09-09, 2018-09-11]
> [2018-09-12, 2018-09-19]
> 
> I make two conditions in order to fetch first if the period 
> [2018-09-01,2018-09-30] contained in the first table.If it exists I will 
> return the sub periods that overlaps the given period
> where data_periods.period && '[2018-09-01,2018-09-30]'::daterange
> and data_sub_periods  && '[2018-09-01,2018-09-30] '::daterange
> 

If data_sub_periods are actually sub periods of period then you should 
need only search for the period [2018-09-01,2018-09-30] and join 
data_periods_info to period on period.id = 
data_periods_info.data_periods_id.


-- 
Adrian Klaver
adrian.klaver@aklaver.com