Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 705
  • Last Modified:

SQL Categories in SQL Server

I am looking to implement a modeled SQL category - by which I mean that an item in my main table is either an entityA or an entityB (where entityA and entityB are mutually exclusive). This is a relatively common occurence in logical ERD's - by way of example consider:

Table: MailingListFilter
NoticeType (PK)
UserID (PK)
IsPublic
IsOptOut
IsBouncing

Table: PublicSubscribers
UserID (PK)
EmailAddress

Table: RegisteredUsers
UserID (PK)
EmailAddress

Each user in MailingListFilter is either public or a registered - they cannot be both.

Whilst it is trivial to model this on an ERD implementing the either/or aspect of the relationship for referential integrity appears to be impossible in SQL Server. I could, in principle, split the main table into two (one for public subscriptions and one for member subscriptions) but then I cannot have a mixed subscriber list for a given NoticeType without dealing with data split across two tables for no really good reason.

Ideas?

Thanks in advance

Alistair
0
agillanders
Asked:
agillanders
  • 3
  • 2
1 Solution
 
lwadwellCommented:
First idea would be to merge the PublicSubscribers and RegisteredUsers table together into one with an additional column UserType and allow that to be a code to represent public or registered.
0
 
agillandersAuthor Commented:
Unfortunately the example kind of simplified the problem. There is a lot of additional information in the member table (which is already a deliberately denormalized combination of two different profile tables (we run two web sited from one codebase and each member profile contains different information. Plus I certainly do not want to create ASP security accounts for public subscribers (the source of the userId's for members).

Plus this is just one example - I have seen, and dodged, this issue on a number of occasions. Within the software world this is a simple generalization. For example a Vehicle object could be a Sedan, an SUV, a Minivan or a Truck. Each of the specializations has some properties in common (inherited from vehicle) but adds their own.

To persist the data in that case you either accept a lot of NULL data for fields combined in a Vehicle's table or have seperate tables for each vehicle type (allowing independant maintenance of the dependant fields for each vehicle type). The latter is better database design but how to establish the relationship between the primary vehicle table and the subordinate vehicle-type-specific tables?

Alternatively I could consider an object oriented database but I am funamentally opposed to that idea - in my book data should be stored reflecting the relationships within the data - not reflecting how I currently choose to use that data! In general data should be capable of a much longer lifespan than the tools that use it !

As I said - in UML it is simple generalization and easy enough to implement. In an ERD it is a simple data categorization - but how to implement this in SQL Server (or any other of the generally poor implementations of the relational model that are out there:-()

Alistair
0
 
agillandersAuthor Commented:
Hmm...my comment on the complication was correct - but on the generalization was probably not (on reflection);. In that situatiuon the relationship is reversed with the 1:M going from vehicle to vehicleType tables. Here the relationship is the other way around but selective. I have seen this often enough - I guess I was just trying to be too clever about drawing parallels! Mea culpa! :-/

Alistair
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lwadwellCommented:
In my many, many years of building and maintaining systems ... I have seen the scenario many times too.  Unfortunately it hard to come up with a specific solution to generic examples if you do not know the other complications.

Other methods that come to mind include:
- use a trigger to enforce the integrity;
- a view would have been nice except SQL Server does not allow views in constraints;
- like you said - split the MailingListFilter into two ... you can always use a view on them to combine as one logical table (... and even INSTEAD OF triggers on the view).
0
 
ZberteocCommented:
Question:

So basically you want to enforce a relationship that will prevent the same UserID to be in both PublicSubscriber and RegisteredUsers at the same time? Is that it?
0
 
agillandersAuthor Commented:
Hmm...two tables plus a union view to access the information as a pseudo table is, I think, the way I'll have to go to enforce referential integrity.

I do see that triggers could also enforce things dynamically but I am buildng this to be maintained by folks without sophisticated SQL Server skills (they're a not-for-profit) so I am having to stay within certain constraints - and giving up triggers/stored procedures is one of them (I know - ouch right?)...it's an Access replacement for now!:-/ It is also the reason I want referential integrity enforced in the data layer - I do not want to leave that responsibility to future code writers!

Thanks

Alistair
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now