Mixing
Representing lists in SQL
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :
- A second table Comments with Content id and Comment
- One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)
- A Text/String column, using a certain separator to seperate comments & comment infos
- Same as above, but using a BLOB
Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !
mysql table array
asked 2 hours ago
LMD
1083
New contributor
LMD 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
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :
- A second table Comments with Content id and Comment
- One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)
- A Text/String column, using a certain separator to seperate comments & comment infos
- Same as above, but using a BLOB
Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !
mysql table array
asked 2 hours ago
LMD
1083
New contributor
LMD 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
up vote
1
down vote
favorite
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :
- A second table Comments with Content id and Comment
- One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)
- A Text/String column, using a certain separator to seperate comments & comment infos
- Same as above, but using a BLOB
Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !
mysql table array
asked 2 hours ago
LMD
1083
New contributor
LMD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :
- A second table Comments with Content id and Comment
- One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)
- A Text/String column, using a certain separator to seperate comments & comment infos
- Same as above, but using a BLOB
Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !
mysql table array
mysql table array
asked 2 hours ago
LMD
1083
New contributor
LMD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
LMD
1083
New contributor
LMD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
LMD
1083
New contributor
LMD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
LMD
1083
asked 2 hours ago
LMD
1083
1083
New contributor
LMD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
LMD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
LMD 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 |Â
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.
Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.
So in my opinion the first option is much better than second.
And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.
answered 38 mins ago
Kondybas
2,03289
add a comment |Â
up vote
2
down vote
Second table for comments is the best option, as you can have many comments by different users at different time interval, which need paginated output during the display.
Separate table for each content is bad idea not only from database side, but also how you are going to identify each content uniquely from frontend itself, so some form of ID is must have need.
Storing comments data in coloumn is too bad. You will be end up having different size of rows for table and moreover you need to perform all operations on comments within your application itself. This will result in poor performance of your application.
answered 55 mins ago
Kapil Bhagchandani
1409
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.
Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.
So in my opinion the first option is much better than second.
And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.
answered 38 mins ago
Kondybas
2,03289
add a comment |Â
up vote
1
down vote
accepted
In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.
Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.
So in my opinion the first option is much better than second.
And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.
answered 38 mins ago
Kondybas
2,03289
add a comment |Â
up vote
1
down vote
accepted
up vote
1
down vote
accepted
In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.
Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.
So in my opinion the first option is much better than second.
And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.
answered 38 mins ago
Kondybas
2,03289
In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.
Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.
So in my opinion the first option is much better than second.
And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.
answered 38 mins ago
Kondybas
2,03289
edited 18 mins ago
answered 38 mins ago
Kondybas
2,03289
answered 38 mins ago
Kondybas
2,03289
answered 38 mins ago
Kondybas
2,03289
2,03289
add a comment |Â
add a comment |Â
up vote
2
down vote
Second table for comments is the best option, as you can have many comments by different users at different time interval, which need paginated output during the display.
Separate table for each content is bad idea not only from database side, but also how you are going to identify each content uniquely from frontend itself, so some form of ID is must have need.
Storing comments data in coloumn is too bad. You will be end up having different size of rows for table and moreover you need to perform all operations on comments within your application itself. This will result in poor performance of your application.
answered 55 mins ago
Kapil Bhagchandani
1409
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
add a comment |Â
up vote
2
down vote
Second table for comments is the best option, as you can have many comments by different users at different time interval, which need paginated output during the display.
Separate table for each content is bad idea not only from database side, but also how you are going to identify each content uniquely from frontend itself, so some form of ID is must have need.
Storing comments data in coloumn is too bad. You will be end up having different size of rows for table and moreover you need to perform all operations on comments within your application itself. This will result in poor performance of your application.
answered 55 mins ago
Kapil Bhagchandani
1409
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Second table for comments is the best option, as you can have many comments by different users at different time interval, which need paginated output during the display.
Separate table for each content is bad idea not only from database side, but also how you are going to identify each content uniquely from frontend itself, so some form of ID is must have need.
Storing comments data in coloumn is too bad. You will be end up having different size of rows for table and moreover you need to perform all operations on comments within your application itself. This will result in poor performance of your application.
answered 55 mins ago
Kapil Bhagchandani
1409
Second table for comments is the best option, as you can have many comments by different users at different time interval, which need paginated output during the display.
Separate table for each content is bad idea not only from database side, but also how you are going to identify each content uniquely from frontend itself, so some form of ID is must have need.
Storing comments data in coloumn is too bad. You will be end up having different size of rows for table and moreover you need to perform all operations on comments within your application itself. This will result in poor performance of your application.
answered 55 mins ago
Kapil Bhagchandani
1409
answered 55 mins ago
Kapil Bhagchandani
1409
answered 55 mins ago
Kapil Bhagchandani
1409
answered 55 mins ago
Kapil Bhagchandani
1409
1409
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
add a comment |Â
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
So if I got you right you are for the 1st option ?
– LMD
21 mins ago
add a comment |Â
LMD is a new contributor. Be nice, and check out our Code of Conduct.
LMD is a new contributor. Be nice, and check out our Code of Conduct.
LMD is a new contributor. Be nice, and check out our Code of Conduct.
LMD 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%2f218311%2frepresenting-lists-in-sql%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
