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 WestPass  1H   Pass  1NTPass  3H   Pass  3NTPass  Pass PassNorth East South West                 1C1S    Pass 3S    Pass4S    Pass Pass  PassSo 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, WestBidOr:Sequence Number, Who, BidMight be some other alternatives but I would like to get some input on the best approach designing the table structure for data like this.
Designing tables for recording rounds in a game
database design
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 gamefrom  bids NT, bids NT1, bids NT2, bids NT3where  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.gameAnd 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.