0

I have a MySQL database that contains these tables (* denotes primary key):

TableA:

----------------------------------------------
|  *id  |  time  |  price  |  special_price  |
----------------------------------------------

TableB:

-------------------------------------
|  *table_a_id  |  *date  |  price  |
-------------------------------------

SpecialDate:

--------------------------
|  *date  |  description |
--------------------------

The value of TableB.price column of each entry in TableB will be either TableA.price or TableA.special_price of the corresponding TableA entry, depending on whether TableB.date is a special date or not (whether TableB.date exists in the SpecialDate table).

This design, as it stands, doesn't feel right as every time I'm adding a record to TableB, I have to look up the date from SpecialDate to see if it's a special date or not, then look up the corresponding entry from TableA to get the value for the price column. And every time a record from SpecialDate gets added or updated, I have to go over every entry in TableB to update its price column.

I'm thinking the column TableB.price should be removed and calculated when needed but I'm not sure how to do that using SQL. I have a lot of views that depend on that price column, for example:

TableAFullView:

-----------------------------------------------------------------------------
|  id  |  time  |  count_of_table_b_instances  |  sum_of_table_b_instances  |
-----------------------------------------------------------------------------

Where count_of_table_b_instances is the number of TableB entries that point to this TableA entry and sum_of_table_b_instances is the sum of their prices.

With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view? Will the performance take a hit?

1
+50

With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view

create table TableA (
id int,
v_time date,
price int,
special_price int
);

create table TableB (
table_a_id int,
v_date date
);

create table SpecialDate (
v_date date,
description varchar(5)
);

insert into TableA values (1,'2019-09-01', 10, 5);
insert into TableB values (1,'2019-10-01');
insert into TableB values (1,'2019-11-01');

insert into TableA values (2,'2019-12-01', 50, 1);
insert into TableB values (2,'2019-12-02');
insert into TableB values (2,'2019-12-03');
insert into TableB values (2,'2019-11-01');

insert into SpecialDate values('2019-11-01', 'sale');

--your view definition

select a.id,
   a.v_time,
   count(*) as count_of_table_b_instances,
   sum(
      case
     when s.v_date is not null then a.special_price
     else a.price
     end
   ) as sum_of_table_b
 from TableA a
 join TableB b
   on a.id = b.table_a_id
 left join SpecialDate s
  on b.v_date = s.v_date
 group by a.id,
      a.v_time

Will the performance take a hit?

I can't say for sure because I can't assume you have the right indexes setup to support this query without seeing the DDL of the tables. If you have indexes in place to support the join clauses, or if you don't have much data in these tables, I don't think you would take a performance hit.

0

The simplest set up is probably something like this:

Table A

-----------------------------
|  *id  |  time  |  price  |  
-----------------------------

Table B

------------------------------------------------------------
|  *id  |  table_a_id(f)  | start_date | end_date | price  |  
------------------------------------------------------------

Which you can then query like this:

SELECT
    table_a.id
    , time
    , coalesce(table_b.price, table_a.price) as price
    , start_date
    , end_date
FROM
    table_a
    left join table_b on table_a.id = table_b.table_a_id
WHERE
    NOW() between start_date and end_date

If you want to 'group' special's together, you could do this:

Table A

-----------------------------
|  *id  |  time  |  price  |  
-----------------------------

Table B

------------------------------------------------------------
|  *id  |  table_a_id(f)  | table_c_id(f) | price  |  
------------------------------------------------------------

Table C

------------------------------------------------------------
|  *id  |  details | start_date | end_date |
------------------------------------------------------------

And query it like:

SELECT
    table_a.id
    , time
    , table_c.details
    , coalesce(table_b.price, table_a.price) as price
    , start_date
    , end_date
FROM
    table_a
    left join table_b on table_a.id = table_b.table_a_id
    left join table_c on table_c.id = table_b.table_c_id
WHERE
    NOW() between start_date and end_date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.