The company I work for sells 2 different software packages. Every time a customer buys our software we give them a licence number. I am in the process of re-designing the database we use to store these licence numbers and who has bought them. The question I have is to do with normalisation.

One of our software packages has a different licence number for every installation on each computer (single user licence), our other software package is sold to a site and can be installed on as many computers as required within that site with only one licence number (site licence).

I am considering storing this information in a database that has 3 main tables, namely:

LicenceNumbers
Customers and
Machines

The problem is that sometimes a licence number will be assigned to a machine and other times it will be given to a customer. A machine will always link to a customer and there may be several machines linked to a customer. This could be true for both types of software because regardless of whether it is a single user licence or a site licence, the customer has to let us know each time they install the software on a computer so that it can be activated. Therefore even with a site licence we know how many machines the customer has installed the software on.

One way to resolve this problem is to have a CustomerID and a MachineID field in the LicenceNumbers table where only one column can have a value in it, the other must be null (check constraint?), another idea is to have an ID column and a Type column again in the LicenceNumbers table, the ID is the primary key of either the customer or machine and the Type distinguishes the table the primary key is in.

Does either of these ideas violate the rules of normalisation? Each row in the LicenceNumbers table does only represent one entity in the real world (albeit that it is assigned sometimes to a computer and other times to a customer). I am drawn to the post Users, Groups and Permissions, that raised a similar question.

Another (perhaps more normalised) approach would be to have a further 2 tables that link machines to licence numbers and customers to licence numbers (the Many-To-Many idea), however with this design how can I enforce the idea of a licence not belonging to both a customer and a machine.

Which design is more suitable, or is there another (perhaps better) approach that fits my requirements?

Regards
Owain Williams

__________________
Regards
Owain Williams

I have just answered your other post, I believe you need one more table to achieve normalization - check that post

Wind is your friend
Matt

Thank you Matt, you are a genius (as far as I am concerned anyway) :)

The other post is a simplified version of this post entitled Referential Integrity. Thanks again.

Regards
Owain Williams