The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Merging records.


I developed a Ticketing software for a tourist department. In it's previous version, it had "TicketID" (Long Integer-Primary Key) field in a main table. Later the department has modified it's working.

Now for a particular tourist place, the tickets will be issued from each gate of entry (if that place has more than one gate of entry). Due to long distance between each gate and rough terrain LAN is not possible. Another trouble is that the place are remote areas where there is no facility of internet connection. So every week, data file will be saved on a CD and then the records will be merged to another database that is located on a computer at the head office.

The problem in this case is that each gate may issue ticket with the same ID, since each gate is running a different copy of my software. I want to know how to deal with this situation, so that the primary key constraint is not violated during merging records.
RPK Send private email
Thursday, September 14, 2006
You should use a) a database and b) a schema that means updating a PK will cascade to foreign keys. Postgres will do this, MySQL will if you use Innodb tables, I'd be shocked if MSSQL couldn't do it...
G Jones
Thursday, September 14, 2006
Sounds like you are using an identity or autonumber autonumber.

How many numbers are there?  How many stations?  How many potential tickets per station?

You can always make the primary key a composite of a staticly populated stationid column and the autonumber ticketid column.  If you don't mind the schema actually being different between the central database and the local databases, you can simply have that column only in the central database and populate it during the load (I don't recommend that approach, however).

Alternatively, if you absolutely have to have a single field, pick a large power of 10 and every ticket would be stationID * 1000000000 + ticketid.  This obviously has a limit of ticketid based on the power of 10 chosen.
Cade Roux Send private email
Thursday, September 14, 2006
If you can live with long ticket numbers (partial input of a ticket id), and a small probability of conflict then look into UUIDs

Allan Wind Send private email
Thursday, September 14, 2006
This may not be practical in your specific circumstance, but when confronted with similar problems, I just assume any imported data is new data.

That is, if I got a CD from Outer Mongolia, I'd read each record (so to speak) into a structure, eliminate the id, and then insert the record into the master database and allow the master to assign a new id. Retain the original creation date and other such date data.

Things you do have to watch out for:

1) Assume the data is transfered in one direction only. If Outer Mongolia corrupts part of their database, you obviously cannot restore those records from the master database. You should clearly establish the backup/restore policy.  Either they maintain their own, or you retain copies of what they send you.

2) Never assume the id number reflects the sequence. Since it's possible for ticket 83674 to have been issued and cashed in before ticket 11134, you will need to make sure the code correctly sorts by creation date or appropriate column.

As for the external identifier - that is, the number stamped on the paper ticket itself - that number probably should reflect the facility and gate it was created at.  For example, OM-02-11134 is the 11,134th ticket issued at gate 2 of the Outer Mongolia Airport.

Internally, it should not matter to the users of the system (including you) what the actual identifier number is.
Thursday, September 14, 2006
In a similar situation, I used a StoreID and an InvoiceID, and made the combination of the two the primary key. Then each individual copy of your software, during installation or first run, is assigned a StoreID.

At the main office, the data is imported using the same two columns as the PK. As long as no duplicate StoreIDs are assigned, the data can always be tracked back to the source store (and can be exported if the individual store's data gets corrupted).
A Real Writer
Friday, September 15, 2006
You could segment your ID space by assigning a range to each gate. That gate can then only assign IDs within that range.

Store the range in the local database instance. Then the software at all gates remains identical, and reads its assigned ID range/generation properties from the database.
Samuel Reynolds Send private email
Tuesday, September 19, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz