wood burning stoves 2.0*
The moose likes JDBC and the fly likes DB structure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB structure" Watch "DB structure" New topic
Author

DB structure

Zein Nunna
Ranch Hand

Joined: Mar 31, 2005
Posts: 245
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

Joined: Jul 31, 2007
Posts: 23
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.


    Never attribute to malice that which is easily explained by incompetence.<br />SCJP (1.5)
    Zein Nunna
    Ranch Hand

    Joined: Mar 31, 2005
    Posts: 245
    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

    Joined: Jul 31, 2007
    Posts: 23
    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".
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: DB structure
     
    Similar Threads
    what kind of web service to use to send XML
    Sorting/Pagination in large datasets
    How can I make this query take less time?
    reading a file record by record
    Is rs.getCount the best option?