Database Schema for a User and his variable number of items

Question

I planning to create tables which hold data related to my users. These tables contain the items that each user owns, and there is no exhaustive list of all the item. The number of items owned by a user might vary from 0 to 100. So the number of items owned by a user is not fixed. For example, A user "Sam" might have "iPod, MacBook, Car, House", while some other user "Dave" might have "Bike, Plane, iPhone, TV ...." etc. So what is the best schema for the tables? I came up with the following idea. Please suggest improvements if any:

I will have a table which holds data as follows:

User Item
========= =========
Sam iPhone
Sam MacBook
Sam Car
Sam House
Dave Bike
Dave Plane
Dave iPhone
Dave TV
.
.

Answer 1

Your problem seems quite simple. Basically as I understand it you need to associate a collection of strings to a user. Your system would work ok, but I would recommend you replace the username with a UserId. That way the cost of updating client facing information is lessened, and you use less memory to store the data.

You didn't specify your target RDBMS but something like the following would work in SQL Server, and give you a good starting point for other RDBMS that use slightly different syntax.

CREATE TABLE dbo.Users -- Added the 's' so it wouldn't be a key word
(
 UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- FK to this instead of the UserName
 UserName NVARCHAR(50) NOT NULL UNIQUE, -- Unique constraint optional if not identifying
 <Other user details>
);
CREATE TABLE dbo.UserStuff
(
 UserStuffId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- Hook for targeted updates/deletes
 UserId INT NOT NULL, -- Not unique so can't be PK
 ItemDescription NVARCHAR(50) NOT NULL, -- Resize to fit needs
 <Other meta data you want like created timestamp>
 CONSTRAINT [FK_UserStuff_UserId_to_Users_UserId] FOREIGN KEY (UserId)
 REFERENCES dbo.Users(UserId)
);

If SELECT performance is poor then you can always add a covering index to the dbo.UserStuff table like so:

CREATE NONCLUSTERED INDEX [IDX_dbo_UserStuff_UserId] ON dbo.UserStuff(UserId) 
 INCLUDE (ItemDescription);