• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
should i keep more than one of these tables,one for customers, transactions, sales etc?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic