6 Replies - 729 Views - Last Post: 06 August 2010 - 03:46 AM Rate Topic: -----

#1 Adola  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 01-August 10

Best way to structure.

Posted 03 August 2010 - 01:22 AM

Evening all, I'm working with SQLITE, and I'm wondering which would be the best way to store a database with users, and a piece of data for every day of the year.

Would it be ok to have a row per user with 365+ columns? Is that highly inefficient, or is that just what a database is for?
Is This A Good Question/Topic? 0
  • +

Replies To: Best way to structure.

#2 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: Best way to structure.

Posted 03 August 2010 - 03:43 AM

I personally wouldn't, a second table with the following structure would probably be best:
---------------
|user_id FK PK|
|date PK |
|data |
---------------
Was This Post Helpful? 1
  • +
  • -

#3 Robin19  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 267
  • View blog
  • Posts: 547
  • Joined: 07-July 10

Re: Best way to structure.

Posted 03 August 2010 - 10:28 AM

What is the nature of the "piece of data"?
Was This Post Helpful? 0
  • +
  • -

#4 Adola  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 01-August 10

Re: Best way to structure.

Posted 03 August 2010 - 05:47 PM

Andy, why have another whole table?

And the data would basically be an integer the user can input every day.

ex:

User Adola's day of numbers:
Day 1 - 3235
Day 2 - 12
Day 3 - 766
Day n - x

etc.
Was This Post Helpful? 0
  • +
  • -

#5 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: Best way to structure.

Posted 03 August 2010 - 11:42 PM

Well firstly the numbers are not data about the user, so should not be stored in the User table, they have a transitive dependancy to the User PK. I think this is a pretty good introduction to normalization.

Also there's a few points to consider - Is the system only going to be used for 365 days? If not, then having 365 columns obviously limits the systems. If what you're planning to do is sort of overwrite the data after 365 days - do you ever really want to delete the old data? There is ways to archive the data so it can be looked back upon later that won't interfere with 'current' data and won't take up too much space on your hard disk.

Also, is every user going to input a number every day? Some database theory suggests that values of NULL should not appear in databases at all (I don't remember who suggested this at all, but I can look it up if you really need the name), having two separate tables will solve that to some extent.

Furthermore, consider how you may want to use this data, using 365 columns will cause chaos trying to do mathematical operations:

SELECT SUM(number)
FROM Numbers
WHERE User_ID = x



Is not possible using 365 columns.

Overall, properly normalizing your database makes your system future proof, every system needs expanding or modifying at some point, an denormalized (0NF) database makes that incredibly difficult. And remember, the way the data is stored in your database and the way you 'project' the information to the users can be completely different - the users do not need to know, and should not know exactly how your information is stored
Was This Post Helpful? 1
  • +
  • -

#6 Adola  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 01-August 10

Re: Best way to structure.

Posted 06 August 2010 - 01:26 AM

Thanks Andy,

I'll start reading over that post you linked. This is my first time with databases, and I'm teaching myself so I'm having a bit of difficulty. I do want to start off with good techniques like normalization.

Quote

Also there's a few points to consider - Is the system only going to be used for 365 days? If not, then having 365 columns obviously limits the systems. If what you're planning to do is sort of overwrite the data after 365 days - do you ever really want to delete the old data? There is ways to archive the data so it can be looked back upon later that won't interfere with 'current' data and won't take up too much space on your hard disk.

-The system would be held indefinitely. It'd be used to allow users in input their daily Calorie intake. Space wouldn't be an issue. I'm concerned with the limits of the database.

Quote

Also, is every user going to input a number every day? Some database theory suggests that values of NULL should not appear in databases at all (I don't remember who suggested this at all, but I can look it up if you really need the name), having two separate tables will solve that to some extent.

- By NULL do you mean:
x = NULL


Because, I have the table defaulting to the value of 0. Not NULL.

Thanks.
Was This Post Helpful? 0
  • +
  • -

#7 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: Best way to structure.

Posted 06 August 2010 - 03:46 AM

Well as far as I know you certainly wouldn't exceed any limits with this database - I'm sure the maximum size of a database is unbound, what I really mean by archiving the data in your tables is for performance benefits if it's going to be a system with many users.

The second table idea makes having a default value of 0 a problem, but the design benefits it gives you outweigh this problem immensely, and there are ways to get around it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1