-1

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?

enter image description here

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?

enter image description here

enter image description here

user3613026
  • 191
  • 1
  • 16
  • 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 04:37
  • Hi. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). 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 05:05
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Nov 16 '18 at 05: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 [mcve]. – philipxy Nov 16 '18 at 05:08

1 Answers1

1

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', ...)
  • seq is 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.
Rick James
  • 135,179
  • 13
  • 127
  • 222