Best way to store results data in database?

Question

I have results data like this:

1. account, name, #, etc
2. account, name, #, etc
...
10. account, name, #, etc

I have approximately 1 set of results data generated each week.

Currently it's stored like so:

DATETIME  DATA_BLOB

Which is annoying because I can't query any of the data without parsing the BLOB into a custom object. I'm thinking of changing this.

I'm thinking of having one giant table:

DATETIME   RANK   ACCOUNT    NAME   NUMBER  ... ETC
  date1      1     user1      nn      #
  date1      2     user2      nn      #
  ...
  date1      10    userN      nn      #
  date2      1     user5      nn      #
  date2      2     user12     nn      #
  ...
  date2      10    userX      nn      #

I don't know anything about database design principles, so can someone give me feedback on whether this is a good approach or there might be a better one?

Thanks

This is a perfectly valid design, provided that all the rows are guaranteed to be unique. — punund, Dec 18 '13 at 02:17

Rock Lee · Answer 1 · 2013-12-18T03:49:15.847

I think it is ok to have a table like that, if there are not one-to-many relationships. In that case, it would be more efficient to have multiple tables like in my example below. Here are some general tips as well:

Tip: Good practice My professor told me that it's always good to have an "ID" column, which is a unique number identifier for each item in the table (1, 2, 3… etc.). (Perhaps that was the intent of your "Number" column.) I think SQLite forces each table to have an ID column anyways.

Tip: Saving storage space - Also, if there is a one-to-many relationship (example: one name has many accounts) then it might save space to have a separate table for the accounts, and then store the ID of the name in the first table- so that way you are storing many ints instead of duplicate strings.

Tip: Efficiency - Some databases have specific frameworks designed to handle relationships such as many-to-one or many-to-many, so if you use their framework for that (I don't remember exactly how to do it) it will probably work more efficiently.

Tip: Saving storage space - If you make your own ID column it might be a waste if it automatically includes an "ID" column anyways - so you might want to check for that possibility.

Conceptual Example: (Storing multiple accounts for the same name)

Poor Solution: Storing everything in 1 table (inefficient, because it duplicates Bob's name, rank, and datetime):

ID  NAME  RANK  DATETIME  ACCOUNT

1   Bob    1      date1      bob_account_1

2   Joe    2      date2      user2_joe

3   Bob    1      date1      bob_account_2

4   Bob    1      date1      bobs_third_account

Better Solution: Having 2 tables to prevent duplicated information (Also demonstrates the usefulness of ID's). I named the 2 tables "Account" and "Name."

Table 1: "Account" (Note that NAME_ID refers to the ID column of Table 2)

ID    NAME_ID  ACCOUNT  

1       1     bob_account_1

2       2     user2_joe

3       1     bob_account_2

4       1     bobs_third_account

Table 2: "Name"

ID  NAME  RANK  DATETIME 

1   Bob    1      date1      

2   Joe    2      date2

I'm not a database expert so this is just some of what I learned in my internet programming class. I hope this helps lead you in the right direction in further research.

Thanks.... This has led me to read some tutorials on database design and I'll be painfully redesigning mine to remove the redundancy between tables and present data in a more easily queried fashion. — graw, Dec 18 '13 at 21:15

Best way to store results data in database?

1 Answers1