MySQL database design for soccer odds
As you know, soccer odds results/history had no end. I want to generate output like this. What is the best practise to design mysql database?
This is what i got so far:
Table:
league: id, league
teams: id, team
odds: id, league_id, date, hometeam_id, awayteam_id, ht, ft,
crown_open_1, crown_open_x, crown_open_2,
crown_closed_1, crown_closed_x, crown_closed_2, bet365_open_1,....
I'm stuck at odds table. How to design for odds table with 1x2 data, open and closed? Or split the data with "|" in 2 column instead of 6?
column: crown_open: 1.82|3.35|4.90
column: crown_closed: 2.07|3.05|4.10
I guess all this approach can achieve my goal, but what is the best practise for long term? Or any other options?
Based on @Rickjames answer, is this correct? Each bookmarker/type has 3 rows, if 4 bookmarkers then 12 rows for each game. Am i right?
mysql database-design relational-database
asked Nov 15 '18 at 11:43
user3613026user3613026
598
add a comment |
As you know, soccer odds results/history had no end. I want to generate output like this. What is the best practise to design mysql database?
This is what i got so far:
Table:
league: id, league
teams: id, team
odds: id, league_id, date, hometeam_id, awayteam_id, ht, ft,
crown_open_1, crown_open_x, crown_open_2,
crown_closed_1, crown_closed_x, crown_closed_2, bet365_open_1,....
I'm stuck at odds table. How to design for odds table with 1x2 data, open and closed? Or split the data with "|" in 2 column instead of 6?
column: crown_open: 1.82|3.35|4.90
column: crown_closed: 2.07|3.05|4.10
I guess all this approach can achieve my goal, but what is the best practise for long term? Or any other options?
Based on @Rickjames answer, is this correct? Each bookmarker/type has 3 rows, if 4 bookmarkers then 12 rows for each game. Am i right?
mysql database-design relational-database
asked Nov 15 '18 at 11:43
user3613026user3613026
598
You should have a new row for each 'open',
– Strawberry
Nov 15 '18 at 12:17
Now to craft theSELECTs. This will help you decide if the schema is 'good'.
– Rick James
Nov 16 '18 at 4:37
Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained.
– philipxy
Nov 16 '18 at 5:05
Possible duplicate of Is storing a delimited list in a database column really that bad?
– philipxy
Nov 16 '18 at 5:06
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 16 '18 at 5:08
add a comment |
As you know, soccer odds results/history had no end. I want to generate output like this. What is the best practise to design mysql database?
This is what i got so far:
Table:
league: id, league
teams: id, team
odds: id, league_id, date, hometeam_id, awayteam_id, ht, ft,
crown_open_1, crown_open_x, crown_open_2,
crown_closed_1, crown_closed_x, crown_closed_2, bet365_open_1,....
I'm stuck at odds table. How to design for odds table with 1x2 data, open and closed? Or split the data with "|" in 2 column instead of 6?
column: crown_open: 1.82|3.35|4.90
column: crown_closed: 2.07|3.05|4.10
I guess all this approach can achieve my goal, but what is the best practise for long term? Or any other options?
Based on @Rickjames answer, is this correct? Each bookmarker/type has 3 rows, if 4 bookmarkers then 12 rows for each game. Am i right?
mysql database-design relational-database
asked Nov 15 '18 at 11:43
user3613026user3613026
598
As you know, soccer odds results/history had no end. I want to generate output like this. What is the best practise to design mysql database?
This is what i got so far:
Table:
league: id, league
teams: id, team
odds: id, league_id, date, hometeam_id, awayteam_id, ht, ft,
crown_open_1, crown_open_x, crown_open_2,
crown_closed_1, crown_closed_x, crown_closed_2, bet365_open_1,....
I'm stuck at odds table. How to design for odds table with 1x2 data, open and closed? Or split the data with "|" in 2 column instead of 6?
column: crown_open: 1.82|3.35|4.90
column: crown_closed: 2.07|3.05|4.10
I guess all this approach can achieve my goal, but what is the best practise for long term? Or any other options?
Based on @Rickjames answer, is this correct? Each bookmarker/type has 3 rows, if 4 bookmarkers then 12 rows for each game. Am i right?
mysql database-design relational-database
mysql database-design relational-database
asked Nov 15 '18 at 11:43
user3613026user3613026
598
asked Nov 15 '18 at 11:43
user3613026user3613026
598
edited Nov 16 '18 at 2:49
user3613026
asked Nov 15 '18 at 11:43
user3613026user3613026
598
asked Nov 15 '18 at 11:43
user3613026user3613026
598
asked Nov 15 '18 at 11:43
user3613026user3613026
598
598
You should have a new row for each 'open',
– Strawberry
Nov 15 '18 at 12:17
Now to craft theSELECTs. This will help you decide if the schema is 'good'.
– Rick James
Nov 16 '18 at 4:37
Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained.
– philipxy
Nov 16 '18 at 5:05
Possible duplicate of Is storing a delimited list in a database column really that bad?
– philipxy
Nov 16 '18 at 5:06
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 16 '18 at 5:08
add a comment |
You should have a new row for each 'open',
– Strawberry
Nov 15 '18 at 12:17
Now to craft theSELECTs. This will help you decide if the schema is 'good'.
– Rick James
Nov 16 '18 at 4:37
Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained.
– philipxy
Nov 16 '18 at 5:05
Possible duplicate of Is storing a delimited list in a database column really that bad?
– philipxy
Nov 16 '18 at 5:06
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 16 '18 at 5:08
You should have a new row for each 'open',
– Strawberry
Nov 15 '18 at 12:17
You should have a new row for each 'open',
– Strawberry
Nov 15 '18 at 12:17
Now to craft the
SELECTs. This will help you decide if the schema is 'good'.– Rick James
Nov 16 '18 at 4:37
Now to craft the
SELECTs. This will help you decide if the schema is 'good'.– Rick James
Nov 16 '18 at 4:37
Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained.
– philipxy
Nov 16 '18 at 5:05
Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained.
– philipxy
Nov 16 '18 at 5:05
Possible duplicate of Is storing a delimited list in a database column really that bad?
– philipxy
Nov 16 '18 at 5:06
Possible duplicate of Is storing a delimited list in a database column really that bad?
– philipxy
Nov 16 '18 at 5:06
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 16 '18 at 5:08
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 16 '18 at 5:08
add a comment |
1 Answer
1
active
oldest
votes
Do not combine multiple values into a single column; they will be hard to split apart. Instead have multiple columns or (usually better) have multiple rows, perhaps in a separate table.
I suspect this might be best: A table with these columns:
league_id, time, home_id, away_id, ht, ft, type, seq, open, closed
Notes:
- type might be `ENUM('crown', 'bet365', ...)
seqis 1,2,3 -- for those 3 columns. (What is the significance of them?)- I don't know what to advise on the "3-0", "1-0" column
- Formatting is the job of the application, not SQL.
- For the above snippet, there would be 24 rows in the table.
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
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%2f53318735%2fmysql-database-design-for-soccer-odds%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Do not combine multiple values into a single column; they will be hard to split apart. Instead have multiple columns or (usually better) have multiple rows, perhaps in a separate table.
I suspect this might be best: A table with these columns:
league_id, time, home_id, away_id, ht, ft, type, seq, open, closed
Notes:
- type might be `ENUM('crown', 'bet365', ...)
seqis 1,2,3 -- for those 3 columns. (What is the significance of them?)- I don't know what to advise on the "3-0", "1-0" column
- Formatting is the job of the application, not SQL.
- For the above snippet, there would be 24 rows in the table.
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
add a comment |
Do not combine multiple values into a single column; they will be hard to split apart. Instead have multiple columns or (usually better) have multiple rows, perhaps in a separate table.
I suspect this might be best: A table with these columns:
league_id, time, home_id, away_id, ht, ft, type, seq, open, closed
Notes:
- type might be `ENUM('crown', 'bet365', ...)
seqis 1,2,3 -- for those 3 columns. (What is the significance of them?)- I don't know what to advise on the "3-0", "1-0" column
- Formatting is the job of the application, not SQL.
- For the above snippet, there would be 24 rows in the table.
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
add a comment |
Do not combine multiple values into a single column; they will be hard to split apart. Instead have multiple columns or (usually better) have multiple rows, perhaps in a separate table.
I suspect this might be best: A table with these columns:
league_id, time, home_id, away_id, ht, ft, type, seq, open, closed
Notes:
- type might be `ENUM('crown', 'bet365', ...)
seqis 1,2,3 -- for those 3 columns. (What is the significance of them?)- I don't know what to advise on the "3-0", "1-0" column
- Formatting is the job of the application, not SQL.
- For the above snippet, there would be 24 rows in the table.
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
Do not combine multiple values into a single column; they will be hard to split apart. Instead have multiple columns or (usually better) have multiple rows, perhaps in a separate table.
I suspect this might be best: A table with these columns:
league_id, time, home_id, away_id, ht, ft, type, seq, open, closed
Notes:
- type might be `ENUM('crown', 'bet365', ...)
seqis 1,2,3 -- for those 3 columns. (What is the significance of them?)- I don't know what to advise on the "3-0", "1-0" column
- Formatting is the job of the application, not SQL.
- For the above snippet, there would be 24 rows in the table.
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
answered Nov 15 '18 at 22:10
Rick JamesRick James
69.5k561102
69.5k561102
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%2f53318735%2fmysql-database-design-for-soccer-odds%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
You should have a new row for each 'open',
– Strawberry
Nov 15 '18 at 12:17
Now to craft the
SELECTs. This will help you decide if the schema is 'good'.– Rick James
Nov 16 '18 at 4:37
Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained.
– philipxy
Nov 16 '18 at 5:05
Possible duplicate of Is storing a delimited list in a database column really that bad?
– philipxy
Nov 16 '18 at 5:06
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 16 '18 at 5:08