Mixing
How to store multiple data in a cell inside MySQL?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ID has multiple dates.
ID Date1 Date2 Date3 ... ... ...
1 2018-05-19 2018-04-09 2018-08-09
2 2016-01-30 2017-05-04 2016-06-07 2020-05-17 2021-06-23 ...
3 2001-05-06
4 2018-11-05 2013-06-09
What is the best way to store multiple dates for a given id?
mysql database-design
edited 2 hours ago
a_horse_with_no_name
37.3k771109
asked 3 hours ago
Prasenjit Saha
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
favorite
I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ID has multiple dates.
ID Date1 Date2 Date3 ... ... ...
1 2018-05-19 2018-04-09 2018-08-09
2 2016-01-30 2017-05-04 2016-06-07 2020-05-17 2021-06-23 ...
3 2001-05-06
4 2018-11-05 2013-06-09
What is the best way to store multiple dates for a given id?
mysql database-design
edited 2 hours ago
a_horse_with_no_name
37.3k771109
asked 3 hours ago
Prasenjit Saha
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Why not solve this relationally with a child table?
– Colin 't Hart
3 hours ago
Store pairs (ID - Date), where the pair is unique whereas each separate field is not.
– Akina
2 hours ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ID has multiple dates.
ID Date1 Date2 Date3 ... ... ...
1 2018-05-19 2018-04-09 2018-08-09
2 2016-01-30 2017-05-04 2016-06-07 2020-05-17 2021-06-23 ...
3 2001-05-06
4 2018-11-05 2013-06-09
What is the best way to store multiple dates for a given id?
mysql database-design
edited 2 hours ago
a_horse_with_no_name
37.3k771109
asked 3 hours ago
Prasenjit Saha
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ID has multiple dates.
ID Date1 Date2 Date3 ... ... ...
1 2018-05-19 2018-04-09 2018-08-09
2 2016-01-30 2017-05-04 2016-06-07 2020-05-17 2021-06-23 ...
3 2001-05-06
4 2018-11-05 2013-06-09
What is the best way to store multiple dates for a given id?
mysql database-design
mysql database-design
edited 2 hours ago
a_horse_with_no_name
37.3k771109
asked 3 hours ago
Prasenjit Saha
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 hours ago
a_horse_with_no_name
37.3k771109
asked 3 hours ago
Prasenjit Saha
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 hours ago
a_horse_with_no_name
37.3k771109
edited 2 hours ago
a_horse_with_no_name
37.3k771109
edited 2 hours ago
a_horse_with_no_name
37.3k771109
37.3k771109
asked 3 hours ago
Prasenjit Saha
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 hours ago
Prasenjit Saha
61
asked 3 hours ago
Prasenjit Saha
61
61
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Prasenjit Saha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Why not solve this relationally with a child table?
– Colin 't Hart
3 hours ago
Store pairs (ID - Date), where the pair is unique whereas each separate field is not.
– Akina
2 hours ago
add a comment |Â
3
Why not solve this relationally with a child table?
– Colin 't Hart
3 hours ago
Store pairs (ID - Date), where the pair is unique whereas each separate field is not.
– Akina
2 hours ago
3
3
Why not solve this relationally with a child table?
– Colin 't Hart
3 hours ago
Why not solve this relationally with a child table?
– Colin 't Hart
3 hours ago
Store pairs (ID - Date), where the pair is unique whereas each separate field is not.
– Akina
2 hours ago
Store pairs (ID - Date), where the pair is unique whereas each separate field is not.
– Akina
2 hours ago
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
2
down vote
Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.
It is likely that you would be better storing the dates in separate rows like so:
ItemDates
===========
ID (PK)
ItemId
Date
so:
ID Item Date
1 1 2018-05-19
2 1 2018-04-09
3 1 2018-08-09
4 2 2016-01-30
5 2 2017-05-04
6 2 2016-06-07
7 2 2020-05-17
8 2 2021-06-23
9 3 2001-05-06
10 4 2018-11-05
11 4 2013-06-09
Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.
If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV patter, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.
answered 2 hours ago
David Spillett
21.7k23167
add a comment |Â
up vote
1
down vote
In terms of efficiency, what others have suggested (child tables) would be the most efficient method, however there are ways to do what you've requested.
Functions
Have you checked out this function: https://stackoverflow.com/a/20738194/8763997 ?
This will automatically group (concatenate) values if they have a non-distinct column relevant to them.
Manual Method
Another idea is you could manually store these dates in a string column and separate them by a delimiter such as comma (,).
Example:
ID Dates
1 2018-01-01
2 2018-03-01,2018-04-02
Then in your application/stored procedure or what ever reads these values, you can then separate them out again.
To insert:
INSERT INTO TableName_A
SELECT ColumnName + ',' + @NewValue
FROM TableName_A
Or update:
UPDATE TableName_A
SET ColumnName = ColumnName + @NewValue
WHERE ColumnName = @ColumnName
edited 8 mins ago
hot2use
7,80551952
answered 2 hours ago
Hex
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.
It is likely that you would be better storing the dates in separate rows like so:
ItemDates
===========
ID (PK)
ItemId
Date
so:
ID Item Date
1 1 2018-05-19
2 1 2018-04-09
3 1 2018-08-09
4 2 2016-01-30
5 2 2017-05-04
6 2 2016-06-07
7 2 2020-05-17
8 2 2021-06-23
9 3 2001-05-06
10 4 2018-11-05
11 4 2013-06-09
Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.
If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV patter, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.
answered 2 hours ago
David Spillett
21.7k23167
add a comment |Â
up vote
2
down vote
Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.
It is likely that you would be better storing the dates in separate rows like so:
ItemDates
===========
ID (PK)
ItemId
Date
so:
ID Item Date
1 1 2018-05-19
2 1 2018-04-09
3 1 2018-08-09
4 2 2016-01-30
5 2 2017-05-04
6 2 2016-06-07
7 2 2020-05-17
8 2 2021-06-23
9 3 2001-05-06
10 4 2018-11-05
11 4 2013-06-09
Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.
If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV patter, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.
answered 2 hours ago
David Spillett
21.7k23167
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.
It is likely that you would be better storing the dates in separate rows like so:
ItemDates
===========
ID (PK)
ItemId
Date
so:
ID Item Date
1 1 2018-05-19
2 1 2018-04-09
3 1 2018-08-09
4 2 2016-01-30
5 2 2017-05-04
6 2 2016-06-07
7 2 2020-05-17
8 2 2021-06-23
9 3 2001-05-06
10 4 2018-11-05
11 4 2013-06-09
Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.
If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV patter, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.
answered 2 hours ago
David Spillett
21.7k23167
Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.
It is likely that you would be better storing the dates in separate rows like so:
ItemDates
===========
ID (PK)
ItemId
Date
so:
ID Item Date
1 1 2018-05-19
2 1 2018-04-09
3 1 2018-08-09
4 2 2016-01-30
5 2 2017-05-04
6 2 2016-06-07
7 2 2020-05-17
8 2 2021-06-23
9 3 2001-05-06
10 4 2018-11-05
11 4 2013-06-09
Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.
If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV patter, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.
answered 2 hours ago
David Spillett
21.7k23167
answered 2 hours ago
David Spillett
21.7k23167
answered 2 hours ago
David Spillett
21.7k23167
answered 2 hours ago
David Spillett
21.7k23167
21.7k23167
add a comment |Â
add a comment |Â
up vote
1
down vote
In terms of efficiency, what others have suggested (child tables) would be the most efficient method, however there are ways to do what you've requested.
Functions
Have you checked out this function: https://stackoverflow.com/a/20738194/8763997 ?
This will automatically group (concatenate) values if they have a non-distinct column relevant to them.
Manual Method
Another idea is you could manually store these dates in a string column and separate them by a delimiter such as comma (,).
Example:
ID Dates
1 2018-01-01
2 2018-03-01,2018-04-02
Then in your application/stored procedure or what ever reads these values, you can then separate them out again.
To insert:
INSERT INTO TableName_A
SELECT ColumnName + ',' + @NewValue
FROM TableName_A
Or update:
UPDATE TableName_A
SET ColumnName = ColumnName + @NewValue
WHERE ColumnName = @ColumnName
edited 8 mins ago
hot2use
7,80551952
answered 2 hours ago
Hex
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
In terms of efficiency, what others have suggested (child tables) would be the most efficient method, however there are ways to do what you've requested.
Functions
Have you checked out this function: https://stackoverflow.com/a/20738194/8763997 ?
This will automatically group (concatenate) values if they have a non-distinct column relevant to them.
Manual Method
Another idea is you could manually store these dates in a string column and separate them by a delimiter such as comma (,).
Example:
ID Dates
1 2018-01-01
2 2018-03-01,2018-04-02
Then in your application/stored procedure or what ever reads these values, you can then separate them out again.
To insert:
INSERT INTO TableName_A
SELECT ColumnName + ',' + @NewValue
FROM TableName_A
Or update:
UPDATE TableName_A
SET ColumnName = ColumnName + @NewValue
WHERE ColumnName = @ColumnName
edited 8 mins ago
hot2use
7,80551952
answered 2 hours ago
Hex
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
up vote
1
down vote
In terms of efficiency, what others have suggested (child tables) would be the most efficient method, however there are ways to do what you've requested.
Functions
Have you checked out this function: https://stackoverflow.com/a/20738194/8763997 ?
This will automatically group (concatenate) values if they have a non-distinct column relevant to them.
Manual Method
Another idea is you could manually store these dates in a string column and separate them by a delimiter such as comma (,).
Example:
ID Dates
1 2018-01-01
2 2018-03-01,2018-04-02
Then in your application/stored procedure or what ever reads these values, you can then separate them out again.
To insert:
INSERT INTO TableName_A
SELECT ColumnName + ',' + @NewValue
FROM TableName_A
Or update:
UPDATE TableName_A
SET ColumnName = ColumnName + @NewValue
WHERE ColumnName = @ColumnName
edited 8 mins ago
hot2use
7,80551952
answered 2 hours ago
Hex
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In terms of efficiency, what others have suggested (child tables) would be the most efficient method, however there are ways to do what you've requested.
Functions
Have you checked out this function: https://stackoverflow.com/a/20738194/8763997 ?
This will automatically group (concatenate) values if they have a non-distinct column relevant to them.
Manual Method
Another idea is you could manually store these dates in a string column and separate them by a delimiter such as comma (,).
Example:
ID Dates
1 2018-01-01
2 2018-03-01,2018-04-02
Then in your application/stored procedure or what ever reads these values, you can then separate them out again.
To insert:
INSERT INTO TableName_A
SELECT ColumnName + ',' + @NewValue
FROM TableName_A
Or update:
UPDATE TableName_A
SET ColumnName = ColumnName + @NewValue
WHERE ColumnName = @ColumnName
edited 8 mins ago
hot2use
7,80551952
answered 2 hours ago
Hex
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 8 mins ago
hot2use
7,80551952
edited 8 mins ago
hot2use
7,80551952
edited 8 mins ago
hot2use
7,80551952
7,80551952
answered 2 hours ago
Hex
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 hours ago
Hex
113
answered 2 hours ago
Hex
113
113
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
add a comment |Â
Prasenjit Saha is a new contributor. Be nice, and check out our Code of Conduct.
Prasenjit Saha is a new contributor. Be nice, and check out our Code of Conduct.
Prasenjit Saha is a new contributor. Be nice, and check out our Code of Conduct.
Prasenjit Saha is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221893%2fhow-to-store-multiple-data-in-a-cell-inside-mysql%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
3
Why not solve this relationally with a child table?
– Colin 't Hart
3 hours ago
Store pairs (ID - Date), where the pair is unique whereas each separate field is not.
– Akina
2 hours ago