Mixing
What is better practice: to store a string or use relations?
Well, I have to choose for myself what practice would be better to use. I try to explain what I mean. For example, I have table Songs:
Id | Name | Artist | Year
---+------+--------+-------
1 | Ad | Bad | 2015
2 | Sad | ads | 2011
3 | Wad | Had | 2012
And also, I want to implement table Playlists. And I don't exactly know, what practice would be better.
Approach #1 - use string to store Songs IDs
Playlists table:
Id | Name | Songs
---+------+------------------------
1 | Main | 1,2
2 | GYM | 4,6,7,8,53,65,76878,78,
3 | Rock | 121,434,655,6767,78
So, to get songs that the some playlist stores, I will parse it every time I'm yielding a list from DB.
Approach #2 - use many-to-many relations strategy
Playlists table:
Id | Name
---+------
1 | Main
2 | GYM
3 | Rock
PlaylistSongs table:
Id | PlaylistId | SongId
---+------------+---------
1 | 1 | 1
2 | 1 | 2
4 | 1 | 344
5 | 1 | 45
6 | 1 | 57
Approach #3 - Your advice
If you know better practice, feel free to share it with me :)
Thanks for attention!
c# sql
sql-server edited Jan 1 at 17:57
marc_s
581k13011201268
asked Jan 1 at 16:45
Lab LabLab Lab
351322
add a comment |
Well, I have to choose for myself what practice would be better to use. I try to explain what I mean. For example, I have table Songs:
Id | Name | Artist | Year
---+------+--------+-------
1 | Ad | Bad | 2015
2 | Sad | ads | 2011
3 | Wad | Had | 2012
And also, I want to implement table Playlists. And I don't exactly know, what practice would be better.
Approach #1 - use string to store Songs IDs
Playlists table:
Id | Name | Songs
---+------+------------------------
1 | Main | 1,2
2 | GYM | 4,6,7,8,53,65,76878,78,
3 | Rock | 121,434,655,6767,78
So, to get songs that the some playlist stores, I will parse it every time I'm yielding a list from DB.
Approach #2 - use many-to-many relations strategy
Playlists table:
Id | Name
---+------
1 | Main
2 | GYM
3 | Rock
PlaylistSongs table:
Id | PlaylistId | SongId
---+------------+---------
1 | 1 | 1
2 | 1 | 2
4 | 1 | 344
5 | 1 | 45
6 | 1 | 57
Approach #3 - Your advice
If you know better practice, feel free to share it with me :)
Thanks for attention!
c# sql
sql-server edited Jan 1 at 17:57
marc_s
581k13011201268
asked Jan 1 at 16:45
Lab LabLab Lab
351322
2
Definitely use a many-to-many table, but it only needs to have the song and playlist ids it doesn't need it's own id, unless you want to be able to associate the same song with the same playlist more than once.
– juharr
Jan 1 at 16:47
Absolutely option two. Option one is more like text describing data. It's not relational data. To illustrate, imagine trying to write queries that tried to search playlists for certain songs or join tables together. It would be really ugly with option 1 but easy with option 2.
– Scott Hannen
Jan 1 at 16:48
add a comment |
Well, I have to choose for myself what practice would be better to use. I try to explain what I mean. For example, I have table Songs:
Id | Name | Artist | Year
---+------+--------+-------
1 | Ad | Bad | 2015
2 | Sad | ads | 2011
3 | Wad | Had | 2012
And also, I want to implement table Playlists. And I don't exactly know, what practice would be better.
Approach #1 - use string to store Songs IDs
Playlists table:
Id | Name | Songs
---+------+------------------------
1 | Main | 1,2
2 | GYM | 4,6,7,8,53,65,76878,78,
3 | Rock | 121,434,655,6767,78
So, to get songs that the some playlist stores, I will parse it every time I'm yielding a list from DB.
Approach #2 - use many-to-many relations strategy
Playlists table:
Id | Name
---+------
1 | Main
2 | GYM
3 | Rock
PlaylistSongs table:
Id | PlaylistId | SongId
---+------------+---------
1 | 1 | 1
2 | 1 | 2
4 | 1 | 344
5 | 1 | 45
6 | 1 | 57
Approach #3 - Your advice
If you know better practice, feel free to share it with me :)
Thanks for attention!
c# sql
sql-server edited Jan 1 at 17:57
marc_s
581k13011201268
asked Jan 1 at 16:45
Lab LabLab Lab
351322
Well, I have to choose for myself what practice would be better to use. I try to explain what I mean. For example, I have table Songs:
Id | Name | Artist | Year
---+------+--------+-------
1 | Ad | Bad | 2015
2 | Sad | ads | 2011
3 | Wad | Had | 2012
And also, I want to implement table Playlists. And I don't exactly know, what practice would be better.
Approach #1 - use string to store Songs IDs
Playlists table:
Id | Name | Songs
---+------+------------------------
1 | Main | 1,2
2 | GYM | 4,6,7,8,53,65,76878,78,
3 | Rock | 121,434,655,6767,78
So, to get songs that the some playlist stores, I will parse it every time I'm yielding a list from DB.
Approach #2 - use many-to-many relations strategy
Playlists table:
Id | Name
---+------
1 | Main
2 | GYM
3 | Rock
PlaylistSongs table:
Id | PlaylistId | SongId
---+------------+---------
1 | 1 | 1
2 | 1 | 2
4 | 1 | 344
5 | 1 | 45
6 | 1 | 57
Approach #3 - Your advice
If you know better practice, feel free to share it with me :)
Thanks for attention!
c# sql
sql-server c# sql
sql-server edited Jan 1 at 17:57
marc_s
581k13011201268
asked Jan 1 at 16:45
Lab LabLab Lab
351322
edited Jan 1 at 17:57
marc_s
581k13011201268
asked Jan 1 at 16:45
Lab LabLab Lab
351322
edited Jan 1 at 17:57
marc_s
581k13011201268
edited Jan 1 at 17:57
marc_s
581k13011201268
edited Jan 1 at 17:57
marc_s
581k13011201268
581k13011201268
asked Jan 1 at 16:45
Lab LabLab Lab
351322
asked Jan 1 at 16:45
Lab LabLab Lab
351322
asked Jan 1 at 16:45
Lab LabLab Lab
351322
351322
2
Definitely use a many-to-many table, but it only needs to have the song and playlist ids it doesn't need it's own id, unless you want to be able to associate the same song with the same playlist more than once.
– juharr
Jan 1 at 16:47
Absolutely option two. Option one is more like text describing data. It's not relational data. To illustrate, imagine trying to write queries that tried to search playlists for certain songs or join tables together. It would be really ugly with option 1 but easy with option 2.
– Scott Hannen
Jan 1 at 16:48
add a comment |
2
Definitely use a many-to-many table, but it only needs to have the song and playlist ids it doesn't need it's own id, unless you want to be able to associate the same song with the same playlist more than once.
– juharr
Jan 1 at 16:47
Absolutely option two. Option one is more like text describing data. It's not relational data. To illustrate, imagine trying to write queries that tried to search playlists for certain songs or join tables together. It would be really ugly with option 1 but easy with option 2.
– Scott Hannen
Jan 1 at 16:48
2
2
Definitely use a many-to-many table, but it only needs to have the song and playlist ids it doesn't need it's own id, unless you want to be able to associate the same song with the same playlist more than once.
– juharr
Jan 1 at 16:47
Definitely use a many-to-many table, but it only needs to have the song and playlist ids it doesn't need it's own id, unless you want to be able to associate the same song with the same playlist more than once.
– juharr
Jan 1 at 16:47
Absolutely option two. Option one is more like text describing data. It's not relational data. To illustrate, imagine trying to write queries that tried to search playlists for certain songs or join tables together. It would be really ugly with option 1 but easy with option 2.
– Scott Hannen
Jan 1 at 16:48
Absolutely option two. Option one is more like text describing data. It's not relational data. To illustrate, imagine trying to write queries that tried to search playlists for certain songs or join tables together. It would be really ugly with option 1 but easy with option 2.
– Scott Hannen
Jan 1 at 16:48
add a comment |
3 Answers
3
active
oldest
votes
This is too long for a comment.
PlaylistSongs -- a many-to-many relationship -- is the correct way to store the data. Here are some reasons:
- A column in a SQL table should contain one value, not a multiplicity of values.
- Songs are shared among different play lists. They are their own entity. There is also additional information you want about each song (say when it was released, its genre, and so on).
- Numbers should not be stored as strings in a relational database; they should be stored as some numeric type.
- Foreign key relationships should be properly declared between tables.
- SQL has (in general) pretty bad parsing functions for strings.
- SQL can optimize data stored in the correct format.
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
add a comment |
Surely, a many to many relationship required as the playlist table clearly doesnt have atomic values rather a cluster for same column as PlaylistId in this case. Generally, this is the best practice we follow to encounter data redundancy/duplicates/atomicity(the thumb rule as in this case to apply any normalization (splitting the tables)). Data Integrity constraint be ACID (ATOMICITY, CONSISTENCY, Isolation, Durability)
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
add a comment |
Surely you will go for a man-to-many relationship scenario as you explained in approach # 2 by creating a PlaylistSongs table. This truly fits in as a relational database scenario in an RDBMS. Consider the benefits you get compared to your first approach in following scenarios.
1.) Data Consistency issues, for example, how if you want to delete a song from Songs table that is attached to an album. The first approach would not restrict you from doing that but in the second approach you can create foreign key relationships that would trigger error.
2.) Data Joins. For example, Using SQL you can use Join to get all the songs available in a given album, but in first approach you'll have to first do manual string manipulations first to extract out ids and then get out song records individually (both terrible for programming and hectic on machine)
3.) You have full SQL support at your end to query dozens of scenarios for reporting, for example use Count to get number of songs in a given album etc etc..
Thanks.
answered Jan 1 at 19:39
ObaidObaid
28927
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53997210%2fwhat-is-better-practice-to-store-a-string-or-use-relations%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is too long for a comment.
PlaylistSongs -- a many-to-many relationship -- is the correct way to store the data. Here are some reasons:
- A column in a SQL table should contain one value, not a multiplicity of values.
- Songs are shared among different play lists. They are their own entity. There is also additional information you want about each song (say when it was released, its genre, and so on).
- Numbers should not be stored as strings in a relational database; they should be stored as some numeric type.
- Foreign key relationships should be properly declared between tables.
- SQL has (in general) pretty bad parsing functions for strings.
- SQL can optimize data stored in the correct format.
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
add a comment |
This is too long for a comment.
PlaylistSongs -- a many-to-many relationship -- is the correct way to store the data. Here are some reasons:
- A column in a SQL table should contain one value, not a multiplicity of values.
- Songs are shared among different play lists. They are their own entity. There is also additional information you want about each song (say when it was released, its genre, and so on).
- Numbers should not be stored as strings in a relational database; they should be stored as some numeric type.
- Foreign key relationships should be properly declared between tables.
- SQL has (in general) pretty bad parsing functions for strings.
- SQL can optimize data stored in the correct format.
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
add a comment |
This is too long for a comment.
PlaylistSongs -- a many-to-many relationship -- is the correct way to store the data. Here are some reasons:
- A column in a SQL table should contain one value, not a multiplicity of values.
- Songs are shared among different play lists. They are their own entity. There is also additional information you want about each song (say when it was released, its genre, and so on).
- Numbers should not be stored as strings in a relational database; they should be stored as some numeric type.
- Foreign key relationships should be properly declared between tables.
- SQL has (in general) pretty bad parsing functions for strings.
- SQL can optimize data stored in the correct format.
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
This is too long for a comment.
PlaylistSongs -- a many-to-many relationship -- is the correct way to store the data. Here are some reasons:
- A column in a SQL table should contain one value, not a multiplicity of values.
- Songs are shared among different play lists. They are their own entity. There is also additional information you want about each song (say when it was released, its genre, and so on).
- Numbers should not be stored as strings in a relational database; they should be stored as some numeric type.
- Foreign key relationships should be properly declared between tables.
- SQL has (in general) pretty bad parsing functions for strings.
- SQL can optimize data stored in the correct format.
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
answered Jan 1 at 16:48
Gordon LinoffGordon Linoff
785k35310417
785k35310417
add a comment |
add a comment |
Surely, a many to many relationship required as the playlist table clearly doesnt have atomic values rather a cluster for same column as PlaylistId in this case. Generally, this is the best practice we follow to encounter data redundancy/duplicates/atomicity(the thumb rule as in this case to apply any normalization (splitting the tables)). Data Integrity constraint be ACID (ATOMICITY, CONSISTENCY, Isolation, Durability)
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
add a comment |
Surely, a many to many relationship required as the playlist table clearly doesnt have atomic values rather a cluster for same column as PlaylistId in this case. Generally, this is the best practice we follow to encounter data redundancy/duplicates/atomicity(the thumb rule as in this case to apply any normalization (splitting the tables)). Data Integrity constraint be ACID (ATOMICITY, CONSISTENCY, Isolation, Durability)
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
add a comment |
Surely, a many to many relationship required as the playlist table clearly doesnt have atomic values rather a cluster for same column as PlaylistId in this case. Generally, this is the best practice we follow to encounter data redundancy/duplicates/atomicity(the thumb rule as in this case to apply any normalization (splitting the tables)). Data Integrity constraint be ACID (ATOMICITY, CONSISTENCY, Isolation, Durability)
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
Surely, a many to many relationship required as the playlist table clearly doesnt have atomic values rather a cluster for same column as PlaylistId in this case. Generally, this is the best practice we follow to encounter data redundancy/duplicates/atomicity(the thumb rule as in this case to apply any normalization (splitting the tables)). Data Integrity constraint be ACID (ATOMICITY, CONSISTENCY, Isolation, Durability)
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
edited Jan 1 at 17:02
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
answered Jan 1 at 16:56
Himanshu AhujaHimanshu Ahuja
8922218
8922218
add a comment |
add a comment |
Surely you will go for a man-to-many relationship scenario as you explained in approach # 2 by creating a PlaylistSongs table. This truly fits in as a relational database scenario in an RDBMS. Consider the benefits you get compared to your first approach in following scenarios.
1.) Data Consistency issues, for example, how if you want to delete a song from Songs table that is attached to an album. The first approach would not restrict you from doing that but in the second approach you can create foreign key relationships that would trigger error.
2.) Data Joins. For example, Using SQL you can use Join to get all the songs available in a given album, but in first approach you'll have to first do manual string manipulations first to extract out ids and then get out song records individually (both terrible for programming and hectic on machine)
3.) You have full SQL support at your end to query dozens of scenarios for reporting, for example use Count to get number of songs in a given album etc etc..
Thanks.
answered Jan 1 at 19:39
ObaidObaid
28927
add a comment |
Surely you will go for a man-to-many relationship scenario as you explained in approach # 2 by creating a PlaylistSongs table. This truly fits in as a relational database scenario in an RDBMS. Consider the benefits you get compared to your first approach in following scenarios.
1.) Data Consistency issues, for example, how if you want to delete a song from Songs table that is attached to an album. The first approach would not restrict you from doing that but in the second approach you can create foreign key relationships that would trigger error.
2.) Data Joins. For example, Using SQL you can use Join to get all the songs available in a given album, but in first approach you'll have to first do manual string manipulations first to extract out ids and then get out song records individually (both terrible for programming and hectic on machine)
3.) You have full SQL support at your end to query dozens of scenarios for reporting, for example use Count to get number of songs in a given album etc etc..
Thanks.
answered Jan 1 at 19:39
ObaidObaid
28927
add a comment |
Surely you will go for a man-to-many relationship scenario as you explained in approach # 2 by creating a PlaylistSongs table. This truly fits in as a relational database scenario in an RDBMS. Consider the benefits you get compared to your first approach in following scenarios.
1.) Data Consistency issues, for example, how if you want to delete a song from Songs table that is attached to an album. The first approach would not restrict you from doing that but in the second approach you can create foreign key relationships that would trigger error.
2.) Data Joins. For example, Using SQL you can use Join to get all the songs available in a given album, but in first approach you'll have to first do manual string manipulations first to extract out ids and then get out song records individually (both terrible for programming and hectic on machine)
3.) You have full SQL support at your end to query dozens of scenarios for reporting, for example use Count to get number of songs in a given album etc etc..
Thanks.
answered Jan 1 at 19:39
ObaidObaid
28927
Surely you will go for a man-to-many relationship scenario as you explained in approach # 2 by creating a PlaylistSongs table. This truly fits in as a relational database scenario in an RDBMS. Consider the benefits you get compared to your first approach in following scenarios.
1.) Data Consistency issues, for example, how if you want to delete a song from Songs table that is attached to an album. The first approach would not restrict you from doing that but in the second approach you can create foreign key relationships that would trigger error.
2.) Data Joins. For example, Using SQL you can use Join to get all the songs available in a given album, but in first approach you'll have to first do manual string manipulations first to extract out ids and then get out song records individually (both terrible for programming and hectic on machine)
3.) You have full SQL support at your end to query dozens of scenarios for reporting, for example use Count to get number of songs in a given album etc etc..
Thanks.
answered Jan 1 at 19:39
ObaidObaid
28927
answered Jan 1 at 19:39
ObaidObaid
28927
answered Jan 1 at 19:39
ObaidObaid
28927
answered Jan 1 at 19:39
ObaidObaid
28927
28927
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53997210%2fwhat-is-better-practice-to-store-a-string-or-use-relations%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Definitely use a many-to-many table, but it only needs to have the song and playlist ids it doesn't need it's own id, unless you want to be able to associate the same song with the same playlist more than once.
– juharr
Jan 1 at 16:47
Absolutely option two. Option one is more like text describing data. It's not relational data. To illustrate, imagine trying to write queries that tried to search playlists for certain songs or join tables together. It would be really ugly with option 1 but easy with option 2.
– Scott Hannen
Jan 1 at 16:48