Member Avatar for Pamilerin

I am currently working on a stock market monitoring application and I was wondering if anyone cold help me by giving me a guideline on how to create a new portfolio(sort of a personal account) for each user that accesses the system such that 2 different users do not have the same portfolio.
I am making use of SQL server for my database if that comes in helpful. Thank you.

Edited by Pamilerin
  • 2 Contributors
  • 3 Replies
  • 114 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by thines01

Dani AI

Generated

A reliable pattern is to enforce ownership and uniqueness in the database, and let the application perform a simple, idempotent "create-if-not-exists" operation. As pointed out, having explicit IDs is correct — the important bit is a DB-level constraint so two concurrent requests cannot create duplicate portfolios for different users.

A minimal SQL schema (one portfolio per user) looks like:

CREATE TABLE Users (
  UserId INT IDENTITY(1,1) PRIMARY KEY,
  Email NVARCHAR(256) NOT NULL UNIQUE
);

CREATE TABLE Portfolios (
  PortfolioId INT IDENTITY(1,1) PRIMARY KEY,
  UserId INT NOT NULL UNIQUE,
  Name NVARCHAR(100),
  CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT FK_Portfolio_User FOREIGN KEY (UserId) REFERENCES Users(UserId)
);

Application workflow: try to insert and return the new PortfolioId; if a duplicate-key error occurs (another request created it concurrently), read and return the existing row. This avoids race conditions while keeping logic simple. Example VB.NET pattern:

Public Function CreatePortfolio(userId As Integer, name As String) As Integer
  Dim sql = "INSERT INTO Portfolios (UserId, Name) OUTPUT INSERTED.PortfolioId VALUES (@UserId, @Name)"
  Using cn As New SqlConnection(connString)
    Using cmd As New SqlCommand(sql, cn)
      cmd.Parameters.AddWithValue("@UserId", userId)
      cmd.Parameters.AddWithValue("@Name", name)
      cn.Open()
      Try
        Return Convert.ToInt32(cmd.ExecuteScalar())
      Catch ex As SqlException
        If ex.Number = 2601 OrElse ex.Number = 2627 Then
          cmd.CommandText = "SELECT PortfolioId FROM Portfolios WHERE UserId = @UserId"
          Return Convert.ToInt32(cmd.ExecuteScalar())
        End If
        Throw
      End Try
    End Using
  End Using
End Function

Extra notes: choose INT IDENTITY for compact indexes; use UNIQUEIDENTIFIER/NEWSEQUENTIALID only for distributed scenarios. If multiple portfolios per user are required, replace the UNIQUE(UserId) with a composite unique index on (UserId, PortfolioName). Always use parameterized SQL or an ORM, enforce foreign keys, and handle duplicate-key errors gracefully rather than relying on a pre-check SELECT alone. This addresses the original requirement from while keeping concurrent creation safe and simple.

Recommended Answers

All 3 Replies

Member Avatar for thines01

Is it not good enough to use user IDs or Portfolio IDs?

Member Avatar for Pamilerin

I am following you on that, but on the development part, is it that I create a new class for a portfolio and then call it up for each new account created

Member Avatar for thines01

Yes.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.