1

I have a database query. I've named it roads_vw.

After the query has been used for some time, I realize that I need to split the query into subsets: roads_with_spatial_column_vw and roads_without_spatial_colum_vw. Once the change is made, I need to update anything that referenced the old query to use the name of one of the new queries.

This might seem a bit silly, but it happens all the time when I'm designing things, especially databases.

Is there a word or phrase that I could use to succinctly describe this situation?

  • 4
    It sounds rather like refactoring... – Hellion Jun 2 '17 at 15:40
  • Refactoring is a good word since you're renaming and updating the references accordingly. Or you can may be use data segregation. Actually you shouldn't create two tables. You should keep the roads table and assign properties to the data. So you don't need to update the references. – Lucky Jun 2 '17 at 16:03
  • This sounds a bit like de-normalization. In a normalized database schema you’d have a separate table forX and just include the ids from the roads table that had that attribute. – Jim Jun 2 '17 at 16:14
  • @Jim and Lucky: You make fair points. In hindsight, I shouldn't have dumbed-down my real-world example. I thought dumbing it down to the roads_with_x example would make the question simpler, but it didn't really. I've reverted the question back to my real-world example (although it is now quite specific/technical). I'm hoping the the root question is still simple enough. – Wilson Jun 2 '17 at 16:24
  • Disaggregating? I've heard the word but I don't know if what I heard was a correct use of the word. – aparente001 Jun 3 '17 at 4:44
3

Yes, there is. It's called "partitioning". Colloquially you may also hear it called sharding. In PostgreSQL 10, you do it like,

CREATE TABLE roads (
  -- stuff
  has_x bool NOT NULL
) PARTITION BY LIST (has_x);

CREATE TABLE roads_with_x
   PARTITION OF roads
   FOR VALUES IN (true);

CREATE TABLE roads_without_x
   PARTITION OF roads
   FOR VALUES IN (false);

This is also nice because it obscures the underpinnings eliminating the need for you to write triggers.

  • To be fair to Evan, this answer pertains to an old example that I had in my question. In a stoke of bad luck, I changed the example in my question (for reasons described in the comments) right when he was in the process of answering the question. I think the answer still generally holds though, and will mark it as correct. – Wilson Jun 5 '17 at 13:35

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.