I am unsure what is the correct practice here. I have a collection of rows with fixed values in the columns....consider them fixed attributes for a particular object of a given ID. So i guess you could say a read-only table.

Now I am also about to add a sizable number of more columns of which their values change (well over 30 columns most likely as the project progresses). Rows won't be deleted or inserted frequently, as there is one row per object, similar to the read-only table.

This will increase the number of columns by quite a large amount, of which, I read large column counts is not ideal. So i was wondering should i separate these columns to another table so that fixed values are in one table, changing values in another?

Are there any benefits to doing this? Or is there no point for something with less than 50 columns in a table?

I don't know the terminology for a lot database stuff, I am self taught from google. So am looking for advice from people who know how to structure databases to work at their best.

  • 1
    Best choice split table by some business rule - like one set of properties in one table, second in other and etc. Read little about "normal forms" (normalisation). Also MySQL has restriction for total lengths of all indexed columns. and if You want use indexes - it also could be restriction. – a_vlad Jan 2 '16 at 7:01

One concern with a large column counts is to do with the number of rows which can fit in a page. DBMS performs IO one fixed-size page at a time. If any part of a page is needed, all of it is read into memory where the required bit is processed. So, if there are a small number of short columns many rows can fit in a single page. Conversely tables with many, long columns can only fit a few rows per page and more IO is required to retrieve a given number of rows.

Is this good or bad? Well, it depends. (If you're new to database design you'd better get used to hearing that phrase!) If you only ever look for a single row at a time it would be better to have all the columns in single table so one IO will return all the values to memory. They are likely to stay there for a while, meaning subsequent actions on the row will not have to do physical IO. This is great for a typical transaction processing workload where an item goes through a number of steps until it reaches completion.

If your workflow is more BI oriented, where you need sums and averages and whatnot, then a query will retrieve many rows and will benefit from having as many rows per page as possible. Ideally, those rows should only have the columns required to answer the query. Other columns would be better held in a secondary table.

Sadly, few systems are this cut-and-dried. Often there is a mix of OLTP and BI work. No one query is so much more important than the others that it drives the DB design. The additional IO cost of referencing a secondary table can be troublesome. This is where the art takes over from the science of being a DBA. You have to understand the intended usage and juggle options accordingly.

For my two cents, without any understanding of your actual workload whatsoever, I'd suggest you keep everything in a single table for now. Anything else is premature optimisation. When you run into an actual problem you can't solve another way, that's the time to split tables. The additional complication of keeping the secondary tables synchronised with the primary can be surprisingly tricky.

Before then covering indexes will help a lot. Materialised views are useful, too. Neither are free lunches, of course, so understand the costs and benefits before deploying either.

Should you decide to split over several tables I'd suggest keeping together those columns which are used together in a single business action. Mixing read-only and updatable columns in a table is not a problem. I can think of no advantage of separating them on those lines only.

| improve this answer | |
  • Alrighty, i'll keep it to the same table ! :) – Dave Jan 3 '16 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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