# Designing tables for recording rounds in a game

I'm working on website for the game of bridge and I am process to designing the table to store the bidding.

Shortly about the bidding. 4 people at a table will bid clockwise until 3 players has passed so it could be many rotations of bidding with a one of the four to start the bidding like this:

``````North East South West
Pass  1H   Pass  1NT
Pass  3H   Pass  3NT
Pass  Pass Pass

North East South West
1C
1S    Pass 3S    Pass
4S    Pass Pass  Pass
``````

So obviously I need save each bid in sequence but I have a few options how to design this.

Could have a table like this and just keep adding an entry per round needed: Round, NorthBid, EastBid, SouthBid, WestBid

Or: Sequence Number, Who, Bid

Might be some other alternatives but I would like to get some input on the best approach designing the table structure for data like this.

• What are you going to do with the data? Is this for display only? or is there some analytics that you want to be able to do based on it (how many times is '3NT' the accepted bid?)?
– user40980
Commented Apr 15, 2014 at 14:29
• At this stage only for presentation. Bids are in sequence in 1C, 1D, 1H, 1NT, 2C, 2D etc.. next actual bid always have to bid higher than the last. Commented Apr 15, 2014 at 14:43

If this is only for presentation, then the first one is probably easier to work with.

``` +----------+
| bids     |
|----------|
| game     |
| round    |
| north    |
| south    |
| east     |
| west     |
+----------+
```

And you are done. Easy to insert, easy to extract for presentation.

However, this table isn't normalized and thus some queries against it will be more difficult.

Consider asking the question of the table "when is a 3NT the accepted bid?" The query for this question becomes quite convoluted to the point of someone will hunt you down and say nasty things about your table structure. If this is for presentation only, people likely won't do that... but for any other use... ug.

I was going to try to write that query to demonstrate the issue but then it got more ugly than I really wanted to try to present in here... and I've got a fair bit of tolerance for ugly queries. Seriously, its bad if you want to ask that question.

On the other hand, if its a one row per bid...

``` +----------+
| bids     |
|----------|
| game     |
| order    |
| seat     |
| bid      |
+----------+
```

Well, now I can do a query such as:

``````select game
from
bids NT, bids NT1, bids NT2, bids NT3
where
NT.bid = '3NT'
and NT1.order = NT.order +1 and NT1.bid = 'pass'
and NT2.order = NT.order +2 and NT2.bid = 'pass'
and NT3.order = NT.order +3 and NT3.bid = 'pass'
and NT.game = NT1.game and NT.game = NT2.game and NT = NT3.game
``````

And that would do it. Its not the cleanest one (I didn't do nice joins)... but it works.

You might consider making another table that contains the order of bids (since it is an enumerable list) so that the bid itself could be a number and you could do comparisons based on numbers rather than strings (`foo.bid = bar.bid + 5` would say that the bid was raised to the N+1 value in the same suit such as a bid if `2H` was raised to `3H`).

It really boils down to what you want to do with the data. For presentation, a simple demoralized schema works nicely. For analysis, you need something else that looks at the bids as individual rows.