newbie question about database design

[nblue]

I have a design like this:

CREATE TABLE moving
(
id char(10),
itemsList integer,
....
)
CREATE TABLE itemsList
(
id integer,
itemID integer,
quantity integer,
describtion char (40),
...
)
the id in itemsList will be repeat for each different item. I think it very inefficent, so is there a better design for something like this situation?

[sarehu]

What does

the id in itemsList will be repeat for each different item

mean?

Is that your whole design?

[nblue]

I'm sorry for not making it clear, I learning more about database design and I thought of this situation. How do I store information for multiple items but they all belong to same ID.
For example:
There a moving company that need to store information about each of its moving trip. Where each move will contain a list of items that include in the move and other informatioin such as date, destination... So I make up a main table name MOVING that contain most of the information and ITEMSLIST that connect to the ID in ITEMSLIST table. In ITEMLIST table, it will contain all the items involve in the move. However, the way I have it will repeat for each different items in a single move.

For example, there a move from CA to TX and the moving contain 1 table, 2 chair,...
The data on MOVE table will be:
ID || LIST || Date ....
1 || 3 || 02/15/08 ....

And the data on ITEMSLIST will be:
ID || ITEM || QUANTITY ....
3 || chair || 2 .....
3 || table || 1 ......

As I say, this is just a problem I thought of, so this is my whole design.

[AaronASterling]

I think you want to introduce a third table.

CREATE TABLE Moves
(
    MoveID    int     IDENTITY  NOT NULL PRIMARY KEY,
    Date        datetime            NOT NULL
)

CREATE TABLE Items
(
    ItemID              int                IDENTITY  NOT NULL PRIMARY KEY,
    ItemDescription varchar(30)                   NOT NULL,
)

CREATE TABLE ItemsOnMove
(
     ItemsOnMoveID  int    IDENTITY   NOT NULL PRIMARY KEY,
     MoveID              int                     NOT NULL FOREIGN KEY REFERENCES Moves(MoveID),
     ItemID               int                     NOT NULL FOREIGN KEY REFERENCES Items(ItemID),
     quantity              int                     NOT NULL
)

This way you have one table that keeps track of all moves. You have another table that keeps track of items that can be moved. the third table actually keeps track of items moved on a particular move.

For example:

Moves
---------------------------
MoveID || Date
---------------------------
1 || 01/21/08
2 || 01/22/08
---------------------------

Items
---------------------------------
ItemID || ItemDescription
----------------------------------
1 || Table
2 || Chair
3 || Pool Table
---------------------------------

ItemsOnMove
-------------------------------------------------------
ItemsOnMoveID|| MoveID || ItemID|| Quantity
-------------------------------------------------------
1 || 1 || 2 || 4
2 || 1 || 1 || 1
3 || 1 || 3 || 1
4 || 2 || 1 || 1
5 || 2 || 2 || 2

so for example to look at everything that was moved for Move 1 we can say

SELECT ItemDescription, Quantity 
FROM Items i
INNER JOIN ItemsOnMove iom
ON i.ItemID = iom.ItemID
WHERE MoveID = 1

Which should give the output

ItemDescription || Quantity
--------------------------------
Chair || 4
Table || 1
Pool Table || 1

Does that help? I am new to this myself so further input, corrections, refinements would be much appreciated from my end as well.

Thank you,
Aaron

[nblue]

Thank you Aaron, that design is really clear. However, I have a question, the itemOnMoveID, is it a must to put it in since it does not really get use when doing queries since the look up will base on the moveID to get informations? Is it because having a primary key will help with searching speed so all table should have one ?

[AaronASterling]

Thank you Aaron, that design is really clear. However, I have a question, the itemOnMoveID, is it a must to put it in since it does not really get use when doing queries since the look up will base on the moveID to get informations? Is it because having a primary key will help with searching speed so all table should have one ?

To be honest, I am not entirely sure. I do not believe that it would help with search speed in this example. It seems to be standard practice that every table have an IDENTITY column that serves as the primary key. This may be a good canidate for dropping that convention though. Essentially the primary key serves as an index on that column. If you wanted, you could create an index directly on the MoveID column of the ItemsOnMoveTable. I've heard it said that for this to be a good idea you want to have every row be unique from 90% of the other rows in that column. So in this case for that to be a good move, you just have to be sure that the table will hold at least ten distinct moves, each of about the same size. An index will slow down insert speed for that column as well as requiring additional space so you want to be sure its a good idea. It would run as follows:

CREATE  CLUSTERED
INDEX  MoveIDIndex ON ItemsOnMove(MoveID DESC)
INCLUDE (ItemID, Quantity)

I hope that that helps. The INCLUDE statement is only valid in SQL Server 2005 I believe but will increase query speed even more at the expense of some more disk space.

Also I realized that this site has a forum specifically devoted to database design under the web design section.

Later,
Aaron

[nblue]

Thank you very much for your input. I didn't know about the database design section either. I apolozie for posting the question out of its section. I think I will look more into the database. I'm going to mark this as solve since its original question already been answer.