• Gap Analysis Using Algebra
• Meeting your CCPA needs with Data Classification and Masking
• Analyze SQL Server Results in Excel with Conditional Formatting
• From the SQL Server Central Blogs - SQL Database MI benefits
• From the SQL Server Central Blogs - Data Privacy – Playing your part

• The Triple Update

Data Modeling in a New World

The world is changing in some fundamental ways as we recognize and deal with some of the social changes that are occurring regularly. We have had new countries appear and old ones disappear in my lifetime. Borders are redrawn and political systems change. Many of those are just data changes that we can import from some recognized source.

There are other changes that might change the way we model our database schemas. One example is the concept of gender, which has changed from the simple male and female of my youth. I haven't thought much of this, and many of you might not have as well. No matter what your opinion of gender fluidity is, you should still understand how this might impact software and your database systems.

I was thinking about this while reading an article on designing forms for gender diversity. While the form is important, equally important is how we handle this in the database. A single character might not be enough, but even if you encode various values, what do you use? I haven't seen a standard and likely this area of data capture is still evolving.

I suspect that ISO/IEC 5218 needs to be updated, and until it does, I think many of us that work on schema models ought to think about keeping a list. The UK has recommendations, but they don't appear to have much granularity for the different ways people identify. New Zealand is slightly better, but this does appear to be a thorny question from the modeling perspective.

Much of what we capture gender for is some marketing purpose. This allows data analysts to better understand behavior as a person's gender sometimes provides some understanding of other behavior. While adding in new pronouns, titles, and genders can increase complexity, it's also important from the standpoint of having data that is useful.

With that in mind, it makes sense to not use hard coded constraints in tables, and ensure we have a flexible data model that handles the data we are likely to receive, including some sort of not applicable or prefer not to say answer. As data modelers, our goals should be to accurately capture and record data, and that means we need to adapt to a changing world.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums