Re: identity columns

I'm not one of the c.l.theory experts but I would say that the cardiality of the relationship between a person and there email address is at least 1-to-many to and may even be [1..*] to many (think a husband and wife sharing an email account for some reason). A customer's email address is also likely to change. For these reasons, it is a bad key.

But, we can ignore that and generalize the question to "must I always use a natural key"? I would say that you need to think about this at two levels: your __relational__ design and your __implementation__ design. In your relational design you should strive to model your relations so that that model is as close to reality as possible. So, if you know that some subset of attributes of each relation is unique and invariant, that is going to be a key for that relation. If you don't have such a subset of unique invarient attributes, you should probably ask yourself why not. This is when a unique identifier (e.g. customer id) will infect your relational design.

When you implement your design, you should stay as close your your relational design as possible. However, you might have to "break some relational rules" in order to satisfy your application's requirements. I don't see how anybody can argue with this because it seems obvious to me that a usable design that isn't perfectly relational is preferable to a perfectly relational design that isn't usable.

But, I really recommend that you get an expert's opinion about this.

• Brian