Need Compound Key and Unique Constraint

[winshent]

Hi Guys

I am trying to consolidate our Booking databases into one... And I have a problem which comes from the data.

Currently, an individual booking databse is setup for each Event that our company hosts.

The old structure the following key fields

BookingRef: Autonumber
ContactID: Long Integer

What I have designed so far is a table which will store all Bookings for all Events, with the design as below..

BookingRef: Autonumber
ContactID: Long Integer
EventID: Long Integer

Both ContactID and EventID form a Compound Key..

This all would work fine based on a Contact can only have one booking per Event.

My issue now is that looking at the existing booking data to be loaded into the table, there is an overlap with Booking References...

For example.. Booking Ref 1000 exists in both Event A and Event B..

In my head I could work around this by changing field BookingRef to a Long Integer, and enforcing seperate a unique constraint on BookingRef and EventID, whilst still keeping the existing compound key I have created..

Anyone know if this can be done, or alternatively have any other ideas on how to solve this issue

Vince

 

[spikepl]

Another option is to dissociate Bookingref from keys, ie make one up according to whatever rules you may have. And then you'll probably need some primary key for the records, and that could be an autonumber (the current double-function of Bookingref).

In the old db's you copy the value of Bookingref into a Long field and and those values will be retained, when you add you old data to the new. The tricky bit is of course retaining any relations from the old DB based on the original key, whose value now will be the autonumber in the new db.

 

[winshent]

Thanks Spikepl

I could do that, but i need

I want the database to do the work of ensuring data integrity.. A Booking Reference is unique to an Event and the existing booking references cannot change.

I suppose the only other way I can think of to enforce this is to create a seperate table with Booking Ref and EventID as a compound key, but thats a bit more complex and would require a lot of front end redevelopment..


I've just found this, will give it a try, should do what I want

http://stackoverflow.com/questions/5635811/unique-constraint-on-multiple-fields-in-access-2003

 

[spikepl]

Setting up additional constraints seems a bit overkill, since you are the master of creation for new Booking ref (Unless they are created manually? If so, why?)

Don't again fall into the trap of showing keys - in Access they are not considered fit for human consumption. If Booking ref is for human consumption, make it yourself, and use something else for relations.

 

[winshent]

Don't again fall into the trap of showing keys - in Access they are not considered fit for human consumption. If Booking ref is for human consumption, make it yourself, and use something else for relations.

Thanks

I can understand this, but if the system is built properly from scratch then I can't see the harm in it..

I have always developed db's in the past and exposed the keys for human consumption.. plus you can only have one autonumber field on an individual table..

 

[spikepl]

Ah well, you could be right: that discussion is probably of a more religious nature. Still, autonumbers are not the only means for creating keys, especially if the values are visible.

Update: I do note however, that your old db's were based on showing the PK. Had you elected not to, you could easily have married the data into your new DB.