1

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.

Improve this question
2
  • 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?)? 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

1 Answer 1

Reset to default
2

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.