[RESOLVED] Best table design

phpSimon

Hi all

I have a table which stores total revenue, by day, for a number of businesses - a bit like this:

id - INT, PRIMARY
date - DATE
businessname - VARCHAR (30)
totalrevenue - DECIMAL(12,2)

The problem with this is that I update the revenue regularly - more than once a day, which generates mulitple rows per day.

I could delete the existing rows each time I update the revenue for that date, but that seems a bit of a workaround.

I could use UPDATE WHERE date=today. Would that cause an insert if revenue data did not exist for a give date, or would I need to run a query first then INSERT if the data did not exist, and UPDATE if it did?

Or would it be 'best practice' to store the data a different way - ie:

id (PRIMARY
date - DATE
businessname1totalrevenue - DECIMAL(12,2)
businessname2totalrevenue
businessname3totalrevenue

I'd rather keep the table designed as it is for simplicity but if the latter method would be considered the better way it would be quite easy to change.

Thanks!

Simon

softcodes

You can use REPLACE which will insert if does not exist. If it exists then it will delete it and insert the new one all by itself.

phpSimon

Thank you !

phpSimon

Actually, that doesn't SEEM to work.

If I do:

REPLACE INTO revenuetable (businessname, date, totalrevenue) VALUES ('$businessname', '$today', '$revenue')

It still creates new rows because there is a primary key (id) which autoincrements.

Any further suggestions?

Thanks

softcodes

Okay , there are two last ways of doing it :
1. make Date as unique key. then in your insert query use this following technique :-

INSERT INTO table ... VALUES ... ON DUPLICATE KEY UPDATE SET a = .. , b= .. ;

2. Write a stored procedure which checks whether the row already exists and inserts/updates accordingly.

phpSimon

Thanks.

I can't use Date as unique key because there are several rows for each date (one for each business).

I could check for the existence of the row as you suggest, then insert or update - but for one extra step I can just as easily delete any exisiting data, then rewrite it - like this:


$revenuequery = "DELETE FROM revenue WHERE (date='$today' AND businessname='$businessname')";
mysql_query($revenuequery) or die ('Error deleting old revenue');

$addquery = "INSERT INTO revenue (businessname, date, totalrevenue) VALUES ('$businessname', '$today', '$revenueTotal')";
mysql_query($addquery) or die ('Error adding revenue');

Works OK. It just seems a wonky way of doing it, your REPLACE suggestion seemed more logical. Perhaps I need to check my syntax unless anyone has any ideas!

softcodes

Cant endorse that kind of mysql programming.

If date can not be made into a key, take all the columns[or a subset that caters] other than id and make them a composite primary key and then use ON DUPLICATE KEY

Sxooter

Why not just insert a new row for each amount, then sum() them at the end of the day? That way you're not jumping through hoops to upsert data all day, or worrying about race conditions.

phpSimon

Thanks for your comments.

I can't insert a new row on each update then sum() because each update received by the database is a total in itself. It's an update, rather than an addition.

I don't know anything about composite primary keys but after a quick Google I can see the logic and I'll try it.

It does seem surprisingly complicated when normally, MySQL and PHP provide quick, simple solutions for anything - which is why the post topic mentions table design; I thought perhaps the issue was created by poor schema. I can see the most obvious approach would be to make the date the primary key and have a column for each business - but that again seemed more clunky than it needed to be!

phpSimon

Using softcodes advice I have changed the table slightly, removing the primary key column and making a new composite primary key from the date and businessname columns.

The following code then works perfectly.

INSERT INTO revenue SET businessname='$businessname', date='$today', totalrevenue='$revenueTotal'
ON DUPLICATE KEY UPDATE businessname='$businessname', date='$today', totalrevenue='$revenueTotal'