2

I'm developing a simple application where users can keep track of their weight history.

I have the following tables:

user Table

userWeight Table

For the purposes of this demonstation, I will be using user id of 5.

When I want to save a users weight history I do this:

INSERT INTO userWeight (userID, weight) VALUES (5, 76)

This saves the weight into the database.

When I want to retrieve weight history for a specific user I do this:

SELECT weight, timee FROM userWeight WHERE userID = 5

My question is, this seems like a funny way of doing things. Is there a better database design I could use?

With this design, the weights for all users gets stored in a single table, is this the correct approach?

Thank you.

Barmar
  • 741,623
  • 53
  • 500
  • 612
user7782479
  • 21
  • 1
  • 3
    Seems like a reasonable approach to me. – Joe Stefanelli Dec 21 '18 at 21:30
  • 1
    What is the purpose of weightID? I would assume that the user id and the time would be enough to identify the record. – mmking Dec 21 '18 at 21:33
  • @mmking https://stackoverflow.com/questions/7341027/should-every-mysql-table-have-an-auto-incremented-primary-key – Barmar Dec 21 '18 at 21:35
  • 3
    Why do you think it's a funny way of doing things? It seems like the most natural way. You wouldn't want to duplicate the user information in the weights table, since it would be redundant. That's one of the first normalization steps. – Barmar Dec 21 '18 at 21:37
  • 2
    If you were contemplating a separate weight table for each user, put that notion out of your mind immediately. Variable information should be in table data, not table or column names. – Barmar Dec 21 '18 at 21:38
  • @Barmar, It's just that if I have 10,000 users and each of them save their weight 5 times. That's already a table with now 50,000 rows and to me, that seems messy. Thank you though everyone for the responses, glad to know that I'm on the right track! – user7782479 Dec 21 '18 at 21:45
  • 3
    50K is not huge, databases are designed to handle much more than this. You can use database partitioning, and date is often a better partitioning scheme (since older records are often less interesting). – Barmar Dec 21 '18 at 21:50
  • 2
    For comparison, we partition our purchase transaction table by year. Each year's table contains 12 million rows. – Barmar Dec 21 '18 at 21:53
  • `userWeight.userID` should be an index and passwords should be stored as hash [what-data-type-to-use-for-hashed-password-field-and-what-length](https://stackoverflow.com/questions/247304/what-data-type-to-use-for-hashed-password-field-and-what-length). `userWeight.weight` is stored in gramms (int(11) )? If not consider a Decimal. – ComputerVersteher Dec 21 '18 at 22:04
  • 2
    Minor points: (a) SQL statements end in a semicolon `;`. You can get by without it sometimes, but not a good habit. (b) Beware of uppercase/lowercase issues on identifiers such as column name. The SQL standard requires storing the name in all-uppercase, but most systems violate this rule. For portability, I have found all-lowercase to be the safest route. But your use of mixed-case is certainly the most troublesome way to go. – Basil Bourque Dec 21 '18 at 22:37
  • 1
    [Normalized database structure](https://en.wikipedia.org/wiki/Database_normalization) is *not* opinion-based. Just the opposite, it is the closest thing to true engineering in all of software development. Not a proper justification to close this Question. – Basil Bourque Dec 22 '18 at 00:34

1 Answers1

2

Your design is correct because it implements a one-to-many (1:M) relationship between a user and many weights taken at different times.

A simple search confirms this arrangement. For instance, this One-to-many relationship article, or the Wikipedia definition.

Using this model allows you to get a series of weights from a specific user and sort them by time. An implementation where weights are saved on the user table would be incorrect because the number of columns in the user table would have to grow every time a new weight is added.

Denis G. Labrecque
  • 1,023
  • 13
  • 29