Which database table design is better?
Hi Experts,
I've a dilemma, my situation is like this: I'm planning to do a lottery analysis software in java. Basically it is just 10,000 digits namely 4D(digits) from 0000 to 9999. Database would store historical data from year 1990 to present with around a few thousand records. From here, I would need to analyse like which numbers belong to a total of certain numbers (like 1234 would be 10), and so on.
My real problem comes in here, do I store the analysis data together with the historical data? I tried and I would need many many columns like around hundreds of them (just the total analysis example above would need 36 columns and not to mentions tons of other analysis data). This type of design do not need any programming on the client side, just retrieval but I reckon it would pose a lot of problems in future in terms of scalability.
The second solution which I google and read in this forum is just store the historical data in one table and use another table to JUST store the analysis name. Heavy programming and algorithm would be needed on the client side but I'm worried about the processing speed since I'm not well-versed in many algorithm logics. I know this design is good since redundant data is eliminated but I simply have no idea how to link the historical and analysis table together.
Could any industry experts on database structure and algorithm guide me as I'm been scratching my head for a few days.
Many thanks in advance.
[1500 byte] By [
dufera] at [2007-11-26 12:48:06]
# 1
If it's two tables, it sounds as simple as a foreign key relationship. The history is a primary key id, a value, and a date. The analysis records has a primary key id, a value, and foreign key that points back to primary key of the history record that it links to.
I'll assume this is a one-to-one relationship. It can be one-to-many if there are many analysis records for each history record.
It's relational database 101.
%
# 2
I wish we knew more about your requirements. Here are a couple of things I see.,
1) I don't understand what the point of storing the dates at all is.
2) Whether I would store predigested analysis in the database would depend very much on the type of application this is. For example normally I would tell you just to store the raw data however if this was a web application and the point is to display the analysis data then I would process the raw data and store the analysis data. There is no point in repeatedly re-compiling/processing data for a web based reporting style application.
# 3
Sorry for the late reply. I have also thought of putting all the analysis data into the database but the data is simply too many and moreover requests from the visitors could have differrent cominations of analysis data which would make it almost impossible to store all the differerent combinations in database.
One of the requirements is like this:
Requirement: View all draw results with their total sum added to be 20.
Implementation: All my numbers totalling 20 would be stores in a static final array in bean and then querying it from the historical table.
I've applied cotton's idea to just store the raw data and put the processing on the bean side, may I know is it okay in the long run?
# 4
> Sorry for the late reply. I have also thought of
> putting all the analysis data into the database but
> the data is simply too many and moreover requests
> from the visitors could have differrent cominations
> of analysis data which would make it almost
> impossible to store all the differerent combinations
> in database.
>
I am not sure I understand why this is a problem.
Say you had a lottery drawing every single day for the last 20 years. That means you would have 7300 records.
That is a trivial number for even small database.
Doing a query for sums to a single value would be trivial. It would require a table scan but the are only 7300 records.
But if you determined that a lot of queries like that were going to be run every hour (or second) then you should indeed create an analysis table which does the sum for the result.
At some point you are going to run into indexing problems with that approach. One solution is to simply duplicate the data. Again there just isn't enough data that duplicating it is a problem. Each "group" would have duplicated data. If you use the same primary key for the duplicated data it would be trivial (and fast) to do cross group queries as well (like a query for the sums of 20 that also occurred on tuesday.)
Note that it doesn't really matter if you need to create several hundred analysis tables. Again the size is so small that it isn't even meaningfull to discuss sizing the database even then. Your only real concern with that number of tables is ensuring that associations are kept low (no explicit cross group associations.)
# 5
> One of the requirements is like this:> Requirement: View all draw results with their total> sum added to be 20.So when you say you are storing a list of 4 digit numbers, do you actually mean you are storing 4 numbers, each one is a single digit?Ted.
# 6
There would be a total of 36 numbers for total 20 and I planned to store it in an array, they are stored as strings. Would that pose a serious problem?Message was edited by: dufer
# 7
> There would be a total of 36 numbers for total 20 and
> I planned to store it in an array, they are stored as
> strings. Would that pose a serious problem?
Huh?
You have a lottery where each drawing has a total of 36 numbers.
Or each drawing pulls from 36 numbers?
If the second then there is no reason to store an array. You just store the numbers that were pulled.
