I have to create a db design to store blacklisted fax numbers. I have created a table and one column of this table is customerID which is a foreign key to table Customer. Now i want to have a faxnumber which is blacklisted for all customers. How should i proceed with this. Should i use Null in this case. Please provide options
+2
A:
If its blacklisted for all customers then have a separate table for those with no relationships.
There are numbers that are blacklisted for all and those that are blacklisted for certain customers. The number cannot be by definition in both. Thus the numbers that are black listed for certain numbers (and I'm assuming a many:many relationship) you can use the classic many many association table design. For numbers that are blacklisted for all there are no relationships with customers which implies no join conditions. Since the data in each is mutually exclusive each type of data appears in different tables.
I cannot seem to get a image, of how I see the basic design, to display so I'll just paste the link here : http://www.freeimagehosting.net/image.php?9046f1985c.png
Preet Sangha
2010-05-31 08:34:34
U mean to say that create 2 tables one with relationship of customerID and faxnumber and other table for all customers. Won't it be a performance overhead.Please suggest
Rohit
2010-05-31 08:40:59
Could you explain your answer and why you think this is better than just bringing back either ones matching a specific CustomerId or ones where CustomerId is NULL?
Martin Smith
2010-05-31 09:03:31
Agree with Preet's suggestion. If it's a number that doesn't have an associated customer then it should be in a table without a customer Id. Why would you use a null for that?
dportas
2010-05-31 09:58:59
@David. By that logic why use a NULL for anything ever then? Semantically it is still a blacklisted fax number. For the record I'm ambivalent on the best design and don't think there is enough information in the question about the domain requirements to make a clear cut recommendation.
Martin Smith
2010-05-31 13:51:26
Given the complexities and disadvantages of nulls I think you ought to have a good reason to add them to any database design. In this case I don't see a reason. One reason often given for using nulls is that they can be a substitute for "unknown" values - but that does not apply here.
dportas
2010-05-31 13:59:04
@David NULL can also be used to mean N/A. One possible reason why you might not want to split the blacklisted fax numbers is that it adds complexity to queries searching for a number in either table which will now require a UNION. Additionally they might store metadata about the numbers (e.g. reason for blacklisting) and won't be able to use standard integrity constraints to avoid the number appearing twice with conflicting information, again adding complexity.
Martin Smith
2010-05-31 14:45:57
@Martin Not if you put all the numbers in the other table (without Customer Id but with other attributes if necessary). No duplication of other data, no need for a UNION. I don't think it needs to be more complex than that.
dportas
2010-05-31 15:03:26