Add Identity Column for each user?

• dclark45

  Newbie

  Points: 1

  More actions

  June 28, 2010 at 11:05 am

  #147703

  Hi, I'm fairly new to SQL so I'm not sure if what I am trying to accomplish is even possible.

  What I want to do, is for each user reset the identity seed to 1.

  Basically I store the UserID, PurchaseNumber, PurchaseDetails, etc, in the DB.

  For each UserID, I want the PurchaseNumber to start at 1.

  Example:

  UserID = 1

  PurchaseNumber = 1

  for the first order,

  then if they place another:

  UserID = 1

  PurchaseNumber = 2

  Then if UserId=2 places an order

  their first order would be

  UserID = 2

  PurchaseNumber = 1.

  Sorry if I did a bad job explaining this. Any help / hints greatly appreciated!

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  June 29, 2010 at 1:31 am

  #1186193

  Wrong approach, and it will cause unexpected problems for you later when you least need them.

  First, user_id should NOT be an identity column.

  Second, SQL Server will NOT allow more than ONE identity column in a table.

  There is absolutely no need to maintain a separate series of purchase orders for each user, you can easily do that via a view.

  ---

  Regards,

  goodguy

  Experience is a bad teacher whose exams precede its lessons
  

• Ben Teraberry

  SSChampion

  Points: 11815

  More actions

  June 29, 2010 at 12:42 pm

  #1186632

  There is really no need whatsoever of having an order 1, 2, 3 ... for each separate user. Think more in terms of just storing the data reliably and all that information that you want about the order of orders for a particular user will be obtainable through simple queries - this is the power of the relational database.

  Based on your description, I would suggest two tables: one for users and one for orders. The user table will use IDENTITY for the userID and the order table will use IDENTITY for the orderID with a foreign key reference to the userID in the user table. In this way, you maintain the relational nature of SQL Server's relational database design.

  So, for your example you would end up with userID 1 and 2. The order table would be:

  orderID, userID

  1 1

  2 1

  3 2

  Again, keep in mind that the information you want about which order is user 2's first (or 2nd, 3rd, etc) order is obtainable through a query ... the orderID doesn't have any real relevance other than to provide a mechanism by which we can relate data to other data reliably and efficiently.

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]