Best design DB on this scenario

Question

I have a table (prepay_bookings) where I'm putting all the info regarding to prepay bookings and I've been using it for long time so it's quite filled...

Now I want to save the info for the pay on arrival bookings too, this data container is almost the same but without the payment data. That would be 3 or 4 fields less.

What would be the best design?

• a) Creating one new table (arrival_bookings) and use both tables
• b) Add a new field in the existing prepay_bookings called payment_mode and continue filling it but now with both types of bookings.
• c) Other?

If I use b, and I finally end with too many rows.. would this affect performance?

Answer 1

In short - I don't think there is a "best" answer, but there are trade-offs you will need to consider for your specific use case. If it is speed you want - two tables will help for many rows, but there are implications for maintenance and sharing/DRY code.

More detail:

It kind of depends on which mysql engine, but as a general rule it will be faster to select and insert on 2 tables each with half as many rows than one larger table - each will have smaller indexes, and with millions of rows, unless you use clever partitioning rules, this does have performance impacts.

Each table would have its own index - and using an extra field you'd have to index this too. It also depends on the use-case in the code.

If the code has instances where you'd often want a mixed row lookup, I think it would be useful to have them together. If this is never the case, then every select/insert would have to account for the flag.

You could always templatize the DDL (create syntax) for the stuff that is the same. If you are sharing some of the form code and other code - separating them may allow them to drift apart, making that code harder to maintain or less DRY.

Answer 2

This is a classic case of modelling the object oriented concept of inheritance in a relational database.

If I use b, and I finally end with too many rows.. would this affect performance?

Possibly - but in general, databases don't really care if you double the number of records, as long as the queries hit indexes. Only way to find out is to try - on a test system - and see where the bottleneck is.