Database structure advice

Hi all,

I need advice on what to do with an existing table on an existing app.

Right now the table is used to store details about a property and its closing costs. Normally there are always a handful of the same closing costs per property, but we want to have the option to record a lot more of them. We would need to add around 300 more columns to a pretty large table in order to give us the flexibility we want. Adding the new 300 columns to the existing table will bring the total up to almost 600 columns. 95% of the time all of these new columns will be set to 0.

I am at a loss as to what to do. Do I just add them to the table with a default value of 0? Do I create a new table with a belongs_to / has_one relationship that stores these new 300 columns?

Thanks for the advice!

Hey Amanda!

That is a fun challenge to solve. I wouldn't add that many columns to the table as that would get real tricky to manage. I like the idea of doing a new table, that could work well.

The two options I would consider:

1. Use a json column and the store_accessor functionality in Rails to store these values in a json field.
2. Separate table with name and value:integer

Both of these options would let you freely add/remove values as needed. And you can leave the values as nil by default so they don't take up any space if they're not being used.

There aren't too many pros/cons to these. The separate table would just require a join but you can query against json columns in postgres and mysql too so they're similarly equal. I guess if you needed to add any Ruby logic to a value, the separate model would be helpful in that case.