0

I am trying to store different WEIGHTS User's Upload on Different DATES, and when the weights are uploaded they will correspond to a primary ID which is "Username"

So this is what i have a picture of my table looking like

Username(PrimaryKEY)---------- Date ------------ Weight

George------------------------------24/09/2016---------82

Tom-----------------------------------24/09/2016---------85

Harry-----------------------------------24/09/2016--------82

George------------------------------25/09/2016---------83

Tom----------------------------------25/09/2016---------86

Harry---------------------------------25/09/2016--------83

If anyone had any ideas how i could do this differently or infact if this would actually work please let me know, Thanks!

  • Store usernames as PK in a separate table and refer the column as FK in this table, also have a separate id column in this table which will be PK. – Gurwinder Singh Feb 17 '17 at 19:50
  • Typically you'd have two tables e.g. User and Readings with the latter referencing the former. Alternately make the primary key the username and the date. – MikeT Feb 17 '17 at 19:50
  • Generally bad practice to use string type columns as primary key, because they MUST be unique. Instead use an autoincrimented numeric column type, THEN you could add a 2 column UNIQUE index on Username & Date for example – Duane Lortie Feb 17 '17 at 20:26

2 Answers2

1

You have to understand the nature of your DB table before deciding what column(s) should be chosen to be the primary key.

Here is a definition of Primary Key from Techopedia:

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:

  • It must contain a unique value for each row of data.
  • It cannot contain null values.

A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

In this case, it seems like your table is storing measurement information about a user, who is allowed to have multiple measurements on different days (e.g. George had a measurement on 24/09/2016 and then another one again on 25/09/2016), but not on the same day.

In this case, the primary key really should have two columns -- Username and Date.

Do not confuse primary key from unique key or just key (index). Here is an SO discussion about the topic.

By the way, if you want to further allow the same user to upload more than one measurements on the same day, then you have two solutions:

  1. Add a surrogate key column (probably called id) that uniquely identifies each measurement records (see Wikipedia discussion). Use this surrogate key as the primary key of your table.
  2. Make your Date column a type of Datetime rather than just Date. You still have to limit that there can be no more than one measurements from the same user for any given Datetime value (in MySQL the data type of DATETIME support fractional second (up to microseconds precision))

Personally I think the approach of using a surrogate key is the safest in the long run.

Community
  • 1
  • 1
leeyuiwah
  • 6,562
  • 8
  • 41
  • 71
0

I would have a separate table for users.

CREATE TABLE `user` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `weight` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `weight` VARCHAR(45) NOT NULL,
  `date` DATETIME NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `username` FOREIGN KEY `username` (`id`)
    REFERENCES `user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)

This above tables are created with following rules

  1. Foreign key is set to id in user table. If you want to change it to username, change it to. CONSTRAINT username FOREIGN KEY username(username).

  2. On delete is no action so even if a user is deleted the weight record will still be there. If you want to change that, then change on delete to cascade. so if a user is deleted, the weight record by that user will also be deleted.

r0xette
  • 898
  • 3
  • 11
  • 24