How to Handle Optional Columns

My question is related to ServiceASpecificField and ServiceBSpecificField. I feel that these two fields are placed inappropriately because for all records of service A for all subscribers in SubscriberServiceMap table, ServiceBSpecificField will have null value and vice versa.

If开发者_如何学编程 I move these two fields in Subscribers table, then I will have another problem. All those subscribers who only avail service A will have null value in Subscribers.ServiceBSpecificField.

So what should be done ideally?

place check constraint on Service_A and _B tables like:

alter table Service_A add constraint chk_A check (ServiceID = 1);
alter table Service_B add constraint chk_B check (ServiceID = 2);

then jou can join like

select *
from SubscriberService as x
left join Service_A    as a on (a.SubscriberID = x.SubscriberID and a.ServiceID = x.ServiceID)
left join Service_B    as b on (b.SubscriberID = x.SubscriberID and b.ServiceID = x.ServiceID)

An easy way to do this is to ask yourself: Do the values of these columns vary according to the Subscription (SubscriberServiceMap table) or the Service?

If every subscriber of "Service A" has the same value for ServiceASpecificField, only then must you move this to the Services table.

How many such fields do you anticipate? ServiceASpecificField, ServiceBSpecificField, C, D... and so forth? If the number is sizable, you could go for an EAV model, which would involve the creation of another table.

This is a simple supertype-subtype issue which you can solve at 5NF, you do not need EAV or improved EAV or 6NF (the full and final correct EAV) for this. Since the value of ServiceAColumn is dependent on the specific subscriber's subscription to the service, then it has to be in the Associative table.

▶Normalised Data Model◀ (inline links do not work on some browsers/versions.)

Readers who are not familiar with the Relational Modelling Standard may find ▶IDEF1X Notation◀ useful.

• This is an ordinary Relational Supertype-Subtype structure. This one is Exclusive: a Service is exclusively one Subtype.

• The Relations and Subtypes are more explicit and more controlled in this model than in other answers. Eg. the FK Relations are specific to the Service Subtype, not the Service Supertype.

• The Discriminator, which identifies which Subtype any Supertype row is, is the ServiceType. The ServiceType does not need to be repeated in the Subtypes, we known which subtype it is by the subtype table.

• Unless you have millions of Services, a short code is a more appropriate PK than a meaningless number.

Other

• You can lose the Id column in SubscriberService because it is 100% redundant and serves no purpose.

• the PK for SubscriberService is (SubscriberId, ServiceId), unless you want duplicate rows.

• Please change the column names: Subscriber.Id to SubscriberId; Service.Id to ServiceId. Never use Id as a column name. For PKs and FKs, alway use the full column name. The relevance of that will become clear to you when you start coding.

Sixth Normal Form or EAV

Adding columns and tables when adding new services which have new attributes, is well, necessary in a Relational database, and you retain a lot of control and integrity.

If you don't "want" to add new tables per new service then yes, go with EAV or 6NF, but make sure you have the normal controls (type safety) and Data and Referential Integrity available in Relational databases. EAV is often implemented without proper Relational controls and Integrity, which leads to many, many problems. Here is a question/answer on that subject. If you do go with that, and the Data Models in that question are not explanatory enough, let me know and I will give you a Data Model that is specific to your requirement (the DM I have provided above is pure 5NF because that is the full requirement for your original question).

If the value of ServiceSpecificField depends both on service and subscriber and for all subscriber-service pairs the type of the field - is the same (as I see in your example - varchar(50) for both fields), then I would update the SubscriberSerivceMap table only:

table SubscriberSerivceMap:
Id
SubscriberId
ServiceId
SpecificField  

Example of such table:

Id             SubscriberId       Service Id       SpecifiedField
1                 1                   1             sub1_serv1
2                 1                   2             sub1_serv2
3                 2                   1             sub2_serv1
4                 2                   2             sub2_serv2