0

I'm not a DBA so I'm not familiar with the proper lingo, so maybe the title of the question could be a little misleading.

So, the thing. I have Members for a certain system, these members can be part of a demographic segment (any kind of segment: favorite color, gender, job, etc)

These are the tables

SegmentCategory
ID, Name, Description

SegmentCategory_segment
SegmentID, SegmentCategoryID

Segment
ID, Name, Description

MemberSegment
ID, MemberID, SegmentID

So the guy that designed the DB decided to go uber normalizing everything so he put the member's gender on a segment and not in the Member's table.

Is this ok? According to my logic, gender it's a property of the Member so it must be on its entity. But by doing this then there must be duplicated data (The gender on the Member and Gender as a segment) But a trigger on the Member table could just fix this (Update the segment on a gender change)

Having to crawl 4 tables just to get a property from the member seems like over engineering to me.

My question is whether I'm right or not? If so, how could I propose the change to the DBA?

Vector
  • 747
  • 2
  • 11
  • 22
  • Does it model the data as required? it seems so. Unless there is something in the process you are modelling that says that a person (with a gender) might not have a demographic segment, it seems a valid modelling approach. However if you're saying a table has the string 'Gender' in a column somewhere to identify the gender (rather than an actual column), then you're skating close to a EAV (entity attribute value) data model which is scorned and hated by many data modellers. Interesting discussion here: https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ – Nick.Mc Nov 13 '15 at 00:39
  • Your schema doesn't explain what a `Segment` is. This might be a tie into another system and having gender as a segment could be quite important. – Gordon Linoff Nov 13 '15 at 01:53

1 Answers1

1

There isn't a blanket rule you can apply to database decisions like this. It depends on what applications/processes it is supporting. A database for reporting is much easier to work with when it is more de-normalized (in a well thought out way) than it is a more transactional database.

You can have a customer record spread across 2 tables, for instance, if some data is accessed or updated more often than other parts. Say you only need one half of the data 90% of your queries, but don't want to drag around the the varchar(max) fields you have there for whatever reason.

Having said that, having a table with just a gender/memberid is on the far side of extreme. From my naive understanding of your situation I feel you just need a members table with views over top for your segments.

As for the DBA, ultimately I imagine it will be them who will be needing to maintain the integrity of the data, so I would just approach them and say "hey, what do you think of this?" Hopefully they'll either see the merit or be able to give you reasons to their design decisions.

LordBaconPants
  • 1,404
  • 1
  • 19
  • 22
  • Well, I forgot to say `Member` it's the usual user profile data with Name, Birthdate, registration date, etc. Usually gender it's on that entity hence my question. Also getting the users profile would be a more frequent operation than do data analysis with the segments. – Vector Nov 13 '15 at 01:13