aspose file tools*
The moose likes JDBC and the fly likes what is the general practice of using a serial number table? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "what is the general practice of using a serial number table?" Watch "what is the general practice of using a serial number table?" New topic
Author

what is the general practice of using a serial number table?

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 580
should i keep more than one of these tables,one for customers, transactions, sales etc?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

Jacky Luk wrote:should i keep more than one of these tables,one for customers, transactions, sales etc?

Don't use this approach at all if you can help it, because you will spend lots of time chasing race conditions, duplicate fetches or dead-locks and other sources of inconsistency and misery.

If you want a unique ID for e.g. a Customer, use the built-in facilities provided by your database to generate unique numbers. On Oracle/PostgreSQL, you can use a sequence to populate an ID. On MySQL/MariaDB you can define auto-increment columns that can be populated by the database. These solutions will be robust, transaction-safe and scalable. Your hand-crafted solution probably will not.


No more Blub for me, thank you, Vicar.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 580
chris webster wrote:
Jacky Luk wrote:should i keep more than one of these tables,one for customers, transactions, sales etc?

Don't use this approach at all if you can help it, because you will spend lots of time chasing race conditions, duplicate fetches or dead-locks and other sources of inconsistency and misery.

If you want a unique ID for e.g. a Customer, use the built-in facilities provided by your database to generate unique numbers. On Oracle/PostgreSQL, you can use a sequence to populate an ID. On MySQL/MariaDB you can define auto-increment columns that can be populated by the database. These solutions will be robust, transaction-safe and scalable. Your hand-crafted solution probably will not.


Thanks Chris, I've got one more question to ask you.
Say if I have a log table in which there is a id, customerid,
And the maintaience table has a id, customerid
Notice these 2 ids are not identical. By using relational database, they are
generated at different places as you can see, if these 2 ids are not the same
I can't uniquely identify which elog entry belongs to which maintainence record.
How do I solve this issue?
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3447
    
  47

Jacky Luk wrote:I can't uniquely identify which elog entry belongs to which maintainence record.

You can distinguish them only by knowing from which table they came, eg. by storing this information along with the data in your application.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

Not sure I understand your question. If the customer ID is unique for each customer, then it should probably be the primary key for the customer table, so you would generate a fresh unique ID e.g. from a sequence when you create a new customer. The ID for a particular customer should never change.

If you store the customer ID anywhere else, e.g. in a logging table, then you just store the ID value for the customer you're talking about. You do not generate a new customer ID, because you are not creating a new customer, you are just referring to an existing customer.

To be honest, Jacky, judging by the many questions you've asked about database topics, I would advise you to stop trying to code a database application right now, and go and learn about basic relational data modelling instead. Right now you seem to be way out of your depth with this stuff. Go and learn about data modelling, and you'll find it much easier to write database applications.
 
jQuery in Action, 2nd edition
 
subject: what is the general practice of using a serial number table?
 
Similar Threads
delete sql not working
CMR and EJBQL
How to copy a table from one Schema to another Schema table
Coarse grained and Fine Grained
Data from JForm to databse