Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB structure

 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,

I have quite a trivial query.

I need to store a count of a particular entity, for example number of purchases. Hence everytime a customer buys something the purchases count increases for the user.

In my database, should I have a column which keeps track of all 'purchases' or should I just go to the purchase table and perfrom a query, like 'select * from purchases where id=customerID....'

Fundementally what I'm contending with is, I could have a column which keeps count of all transactions or just to count transactions as and when required.
Which one would be better?
Thanks in advance for your thoughts.
Sorry for the convoluted explanation.

Regards
Zein
 
S J Martin
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Zein Nunna:
Hi guys,

I have quite a trivial query.

I need to store a count of a particular entity, for example number of purchases. Hence everytime a customer buys something the purchases count increases for the user.

In my database, should I have a column which keeps track of all 'purchases' or should I just go to the purchase table and perfrom a query, like 'select * from purchases where id=customerID....'

Fundementally what I'm contending with is, I could have a column which keeps count of all transactions or just to count transactions as and when required.
Which one would be better?
Thanks in advance for your thoughts.
Sorry for the convoluted explanation.

Regards
Zein


It's horses for courses, really.

Generally I'd favour doing a count, but....
  • do "select count(column) where column=....."
  • If there's more than 100 or so rows (total) make sure that the column is indexed, perhaps using a binary index if there are only a few possible values for the column.

  • My main reason for favouring a count is that it guarantees that the value will be in sync. Suppose the code had a multi threading / transaction issue whereby sometimes the count didn't get updated.....

    Only when performance became an issue would I decide to keep a separate count to save running an expensive query.
     
    Zein Nunna
    Ranch Hand
    Posts: 245
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Great, Thank you very much SJ Martin.
    I thought I was a little 'out there' on this one. LOL.

    The indexing never crossed my mind. Very useful.

    See my objective is to build something as efficient as possible. I want to do that from the start than to come back later and try to change things.

    Thanks agian.,
    Zein
     
    S J Martin
    Greenhorn
    Posts: 23
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Originally posted by Zein Nunna:
    See my objective is to build something as efficient as possible. I want to do that from the start than to come back later and try to change things.


    Ooooh, I always anticipate a good dose of "refactoring".
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic