customer_1_tbl_seq

[je]

I am new to OpenEMM on MySQL and was wondering why the customer_1_tbl_seq table is used to generate a unique id for the customer_1_tbl when the customer_id column is Auto Incrementing?

[maschoff]

Very good question, indeed! To sum it up: legacy (Oracle, which we used before, uses sequences, MySQL does not).

Both, customer_1_tbl.customer_id and customer_1_tbl_seq are used in OpenEMM:

- field customer_id of customer_1_tbl in classes MailingDaoImpl and RecipientDaoImpl

- customer_1_tbl_seq in classes ImportRecipientsDaoImpl and RecipientDaoImpl

When a new recipient is stored in the database, customer_1_tbl_seq delivers the customer_id (Recipient.DaoImpl.getNewCustomerID) before the recipient is stored in customer_1_tbl (Recipient.DaoImpl.insertNewCust).

A count(customer_id) on customer_1_tbl_seq delivers the number of recipients who are AND have been in the database while a count(customer_id) on customer_1_tbl delivers the number of recipients who are in the database right now.

So, actually, the auto_increment for customer_1_tbl.customer_id is superflous since field customer_id is overwritten anyway. It could be even harmful if someone stores new recipients in customer_1_tbl directly but does not add the same number of entries to customer_1_tbl_seq, because in this case the next to be stored recipient whose ID is taken from customer_1_tbl_seq can not be stored in customer_1_tbl because the ID is alreay taken there.

[je]

My understanding of Oracle sequences and MySQL Autonumbers is that they are, for the most part the same. Minor things like one Autonumber per table aside...

In my mind, it seems like we could do away with the customer_1_tbl_seq table? Recipient.DaoImpl.insertNewCust would
omit the customer_id in the INSERT statement i.e.

INSERT(email, gender, mailtype, firstname, ...) and commit;

So No call to .getNewCustomerID (we'd use whatever AutoIncrement provided), No overwriting, No extra sequence table, no worries about importing new recipients and not updating the sequence table at the same time.

Am I thinking correctly? Missing something? Totally off base?
Thanks for your reply

[maschoff]

You can manipulate sequences in more ways than auto_increment fields (depending on the MySQL version and JSBC driver version, of course).

Yes, one could refactor customer_1_tbl_seq out of RecipientDaoImpl (an the ImportDao class) and delete the table. The sequence was historically used to get the customerID before actually writing the data record into the customer table.

If you want to do the refactoring - go ahead and submit your code!