Database Schema for a User and his variable number of items

database-design

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
.
.

Best Answer

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);

Related Solutions

Database schema for a variable-level hierarchy with variable types

A good way to do this in a relational database is with statement, category, and category_structure tables. The statement represents the line of text. Assume that each statement resides in a single category. A group of statements in a category have to appear in a certain order. Categories include the text groupings including Cardiovascular and Palpitations. The category_structure relates the hierarchy of categories. Each structure has a child_Category, parent_category, and sort_order. A structure is a unique combination of a child and a parent. Here are the table definitions.

statement
PK   statement_ID         number   An autogenerated sequence
FK   category_ID          number   A foreign key to category table
     statement_text       varchar  The long description On a scale of one...
     statement_sort_order number   The order within a category


category
PK   category_ID          number   An autogenerated sequence
  UK category_unique      varchar  Uniquely identifies the purpose of a category.
                                   Values could be Discharge. Also, Chest Pain Symptom, 
                                   Chest Pain Discharge. 
     category_description varchar  The visible text description of a category. The
              values would be Symptoms, Cardiovascular,
              Discharge, Chest Pain, Palpitations. 

category_structure
PK   structure_ID         number  An autogenerated sequence
FKUK child_category_ID    number  foreign key to category
FKUK parent_category_ID   number  foreign key to category. Optional. 
     category_sort_order  number  The order of categories or the order within 
                                  categories

Note the Symptoms category would be the parent of all symptoms. The Discharge category would be the parent of all discharge. The structure table does not prevent a category from having both symptom and discharge as parents.

I see from the reformatting edit that Chest Pain is both a symptom category and a discharge category. This would still work fine. One option is to make two categories for Chest Pain, one as a symptom, one as a discharge. They just happen to have the same name, but they contain different sets of statements. The other option (reflected in the table design) is to add a field to the statement table describing whether the statement is a symptom-style statement or a discharge-style statement. So the Chest Pain category has both symptom statements and discharge statements. The query would only show the appropriate subset based on the need.

Database Design – Inventory Management DB Design

There are some things you want to think about based on the nature of your items and how you store and track them.

Are your items discrete or are they a commodity? The way you would track television sets, each of which has its own distinct serial number is different from how you might track boxes of nails. If your SKUs are discrete you don't have a "quantity" field, instead you track individual items with an intersection table (as suggested by Joe). Otherwise whether you need an intersection table depends on how many different SKUs can be on a shelf (see below).

Is it important to track inventory movements? Do you need to see stock deliveries and shipments? If so you might want to take a kind of double entry accounting approach, treating shelves like GL numbers. If not, a simpler quantity per shelf may be sufficient.

Can multiple SKUs share a shelf? If so, you need an intersection table. If not, you can get by with a foreign key from Shelf to Item.

Does every bin have at least one shelf? Your application (queries) will be much simpler if every bin has at least one shelf, such that you don't need to track inventory which is in a bin but has no applicable shelf information.

EDIT:

It's important to get the terminology clear. When you say "bin" you mean a box without a lid that sits on a shelf. In a lot of inventory situations, a "bin" is a space on a rack where you could put a skid or one or more shelves.

As long as we're clear, you want a hierarchy of SHELVES each of which has many BINS each of which can have many BIN_CONTENTS each of which is an intersection between BIN and ITEM and which has a quantity. Don't store quantity on your ITEM table, store it on the intersection table, that way you can have multiple bins of the same item, or even many different kinds of items, each in their own distinct bins or even multiple different items mixed within a bin, all on the same shelf.

Consider the following ERD:

ERD

Best Answer

Related Solutions

Database schema for a variable-level hierarchy with variable types

Database Design – Inventory Management DB Design

Related Question