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
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
Lee Wadwell
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.
ASKER
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
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
ASKER
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
Alistair
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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
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