9

I have a mysql database in which I have drafts, each of which contains exactly 24 players the order of which matters. I am conflicted between having a drafts table with 24 extra columns for each player or creating a new table for a one-to-many relation between drafts and players.

Drafts

ID Arbitrary info Player1_ID Player2_ID
1 name1 4 6
2 name2 10 6

OR Drafts

ID Arbitrary info
1 name1
2 name2

and Relations

draft_ID Player_ID P_Order
1 4 1
1 6 2
2 10 1
2 6 2

If I were to try to summarize my question, it would be one of efficiency vs elegance/robustness.

If the number of players was variable (and even more so if their order didn't matter), it would be clear to me that I should make another table. (I have found many posts about variable-length arrays that suggest this). However, in my case, adding 24 columns to the draft table seems more efficient to me. I am also thinking of the calls to the database and as of right now, most often calls will be to return the entire draft (i.e. all players), and not many calls relating to specific players. In this small use case, it doesn't matter much which method I choose, but what if things get bigger, what if I had say 1000 players in each draft?

9
  • 7
    When you have 1000 players, you don't want to have a table with 1000 columns.
    – Doc Brown
    Commented Nov 22, 2022 at 14:17
  • 22
    In the mid and long terms, columns are expensive, rows are cheap. Commented Nov 22, 2022 at 15:41
  • 1
    If you need to index these, it's not going to be more efficient. And I don't see a practical way to add uniqueness constraints. For example, how would you prevent having the same player in column 1 or one row and in column 2 in another? I hate wide tables like this, they only create problems.
    – JimmyJames
    Commented Nov 22, 2022 at 16:48
  • 2
    @RobbyG That's easy to enforce in the normalized approach. I'm not very familiar with mysql but I would expect you would need to write a custom trigger to enforce that and it's going to be much slower than a uniqueness constraint on the normalized structure.
    – JimmyJames
    Commented Nov 22, 2022 at 17:41
  • 1
    Perception vs reality is your friend. Implement the easiest RDM according to your DB features. Then create views (or equivalents) to solve "specific" needs. Or choose a different DB. Maybe Cassandra would be more suitable for this.
    – Laiv
    Commented Nov 23, 2022 at 12:20

4 Answers 4

60

"adding 24 columns to the draft table seems more efficient to me"

show me all the drafts which include player 2.

select * from draft where p1=2 or p2=2 or p3=2....

vs

select * from draft left join relation on d.id = r.id where r.playerid=2

Relational databases are designed for the table approach, not the column approach. If you are using a relational database, follow the normalisation rules and add a table.

10
  • What I understand from the point you are making is that it makes the coding easier/clearer, which is part of the answer. But I was wondering about time efficiency/complexity as well. E.g. the 2 versions of the code you wrote would both have linear complexity (in the number of cells) with the one above at MN and the one below at M+MN if M is the number of drafts and N is the number of players per draft. Is it true that all queries would have the same complexity in both cases? (I suspect yes, but I am not sure)
    – RobbyG
    Commented Nov 22, 2022 at 17:41
  • 2
    @RobbyG exactly what use case are you imaging that would make anyone care about the complexity here? Is this a purely academic question or do you have a real cause for concern? Commented Nov 22, 2022 at 19:22
  • 12
    the time complexity is nontrivial to solve here, as the multi column approach will be searching multiple indexes, but with each index having less content. My point is that you can see how even this simple problem is hard to solve with the multi col approach. What if its "drafts with player 2 and 3" or "drafts where player 2 is in a higher order than player 3" etc. You want to avoid calculation in your sql as the database is your congested resource, but a multi col approach forces you to have complex sql queries with multiple terms. The database should be optimised for the standard approach
    – Ewan
    Commented Nov 22, 2022 at 19:45
  • 2
    In your example with a single row containing all the data for the model, You may well find it is faster for retrieving a single model by id. But if that is all you are doing, why use a relational db? why not have a single blob col with json? If you consider various selection criteria, aggregates etc you are bound to find some where its next to impossible to write efficient queries against a multi col or blob approach.
    – Ewan
    Commented Nov 22, 2022 at 19:53
  • 3
    @RobbyG OK, well then what I think we really need to know is WHY you're investigating this kind of performance issue. In the practical world you're causing a lot of pain for very little pay off. Give us a way to make sense of your work and we'll probably give you better answers. Without that we just assume this issue is coming up in a shop that has real work to get done. Commented Nov 22, 2022 at 23:59
23

Unmeasured efficiency concerns are poor arguments for design decisions. If you really believe there is an efficiency issue develop a performance requirement based on your actual needs. Create a test that proves if you’ve met it. Now you aren’t just guessing. You know if a design works.

If that seems like too much work it’s probably because you already know this is a waste of time.

If what you’re looking for is a rule of thumb to follow I prefer designs that don’t force me to cram numbers into names.

Often efficiency comes up as a concern when people are really bothered by something else that they aren’t sure how to express. For example: have you figured out how to limit the number of players?

2

You could take your line of thought further: consider the option of using three columns: Id/Arbitary Info/Players, where "Players" contains a fixed-width byte array of 24*8 bytes (or 24*4 bytes if you have 32-bit player ID keys). In many scenarios where you might estimate that your 24-column schema is more efficient than the relation table, this is even more so.

But for most purposes it's a terrible design, because it forces the database to treat the array as opaque, unstructured data which it cannot process. In fact it is structured in a way that relational databases are well-equipped to process.

The reason you don't use the byte array, I'm guessing, is that you at least want a foreign key constraint on the player IDs. If not that, there is some operation you anticipate needing, which clearly will be horrible or impossible to enforce in this design, and so you've ruled it out.

OK, so what about the 24-column design? It introduces some structure, and at least it allows for foreign key constraints on the 24 player ID columns, but it misses the fact that the 24 different slots in a draw are equivalent for certain purposes. For example, a natural constraint like, "a player must not appear more than once in the same draw" becomes a monstrosity to code. It's conceptually easy enough to write the constraint, for example you could assert 276 pairwise inequalities. You can do something more clever, but whatever you do, you have to explicitly involve all 24 columns. It's complex. In the relation table it's a uniqueness constraint on (draft_ID, Player_ID). It looks simple because it is simple: mathematically speaking, position in an array is a relation, and furthermore it's a function from array, index -> value. So we can express it as a relation in the database, and express the fact it's a function with a uniqueness constraint.

This simplicity just comes from the fact that relations on three variables are in practice easier to talk about than relations on 26 variables, 24 of which are somewhat but not wholly equivalent. If you want the database to do work for you, you have to talk about the relations you've defined. Only if you're going to do all the work yourself and use the database as a dumb bytestore, will any old schema serve equally well.

So, you do need to have some idea of the operations you're likely to perform, before you commit to a less-normalised schema over a more-normalised one, and this isn't just mindlessly conforming to a theoretical idea of what databases "should" look like. It's mainly about identifying actual operations you're going to perform on the data and making a decision what needs to be easy and what can be difficult.

For what it's worth, I can think of one constraint that easier to express in the 24-column schema, which is that every slot in a draw must be occupied. That's just "not null" on 24 columns, whereas with the relation table you'll have to work at it to maintain the property that every draw is fully-populated by 24 players (even if it's as simple as a transaction with 24 inserts, you'll have to consistently use it and not modify the table any other way). Then again, maybe this is not a real constraint, and a partially-populated draw actually is meaningful in your data model. Someone gets a bye, or whatever.

1

One option you have, here assuming that you just want an array of player IDs, and don't need to have any relations using those IDs, is to save the array in a JSON column.

So, instead of:

Drafts

ID Arbitrary info Player1_ID Player2_ID
1 name1 4 6
2 name2 10 6

You would have:

Drafts

ID Arbitrary info Players
1 name1 [4,6]
2 name2 [10,6]

This way you don't need the one-to-many relation, and don't need to add/remove columns in case the number of players change.

3
  • 1
    While I would consider this kind of solution, I would have to admit that it's not ANSI compatible, since types JSON are not standard and depends on the vendor. Querying, updating, etc might or might not be supported for this specific type, treating the type as a mere string. Then comes the issue with drivers and dialects. If the driver/dialect doesn't support these types and the operations related to them, then you can not easily abstract the code from the data store, causing vendor locking in most cases. These are concerning issues to take into account if someone decides to take this route.
    – Laiv
    Commented Nov 23, 2022 at 12:16
  • @Laiv JSON is part of the SQL standard since 2016. Or is ANSI some particular subset that you're referring to?
    – Bergi
    Commented Nov 24, 2022 at 4:07
  • If you were to go with JSON, you'd better go full document-oriented and store the entire player object in the array, not just an id. Arrays of ids are a headache without support for foreign key constraints.
    – Bergi
    Commented Nov 24, 2022 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.