Table Relationship Advise - How to set up? (Two tables) 1

[Cordury2]

Good Afternoon,

I am attempting to create a simple DB with two tables. The first table (tblStoreList) contains the following fields:

Store #, Date, Item, Quantity, SKU, Description, Cost, Comments

The second table (tblRadioInfo) contains the following fields:

Item #, Item, SKU, Description, Cost

Obviously I have some redundant fields and can combine the data into one table in Excel and then import a “master” table.

My issue is that some of the Store Numbers have multiple Items (more than one type of radio). I want the end user to be able to enter a store # and it db will display ALL radios for that store.

Currently, the tblStoreList is set up like:

i.e Store Item SKU Cost
14000 Radio ABC 1234 79.99
14000 Radio DEF 5678 55.00

Any suggestions would be helpful.

Thanks!

 

[johnherman]

It seems you need three tables:
Store table
StoreID
StoreLocation
etc.

Item table
Item#
SKU
Description

StoreItem table
StoreID
Item#
Date
Quantity
Cost

If cost is constant, put cost in Item table.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[johnherman]

Oops, maybe a little more info is needed.
StoreItem is called a bridge table.
The natural key is the combination (concatenation) of StoreID and Item# or it could be assigned an Identity key. The StoreID and Item# are keys to their respective tables from the bridge table.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[Cordury2]

Currently, I now just have one table with all information. There are only about 1,000 records so I am not woried about size.

I created a both a query and a subform that will show the different types of radios for the same store.

Ideally, I would like the user to enter a Store Number in the Store # field or search field and the form would display ALL records for that one store including: Date, Item, Qty, Cost, etc.

 

[lespaul]

you need to read the document link below to get a firm understanding of relationships and how they work. Take all the fields from your Excel sheet and list them:

Store #
Date
Item
Quantity
SKU
Description
Cost
Comments
Item #
Item
SKU
Description
Cost

Now you for each item ask "Is this information about the Store, the Item, or the Items in the Store?" That will help you determine which fields go in which tables.

HTH






Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Cordury2]

Thank you for the link!

 

[johnherman]

lespaul - I like the way you added that link to your signature line - star baby.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[lespaul]

I got so tired of having to recommend it in almost every posting, I just decided to add it to all of them automatically!

thanks for the star!

Les

 

[willir]

Going back to johnherman post...
Please note that he has a many-to-many relationship between item and store to present the inventory for the item at the store.

This is a good way of doing it as per the rules for Normailization as described in Leslie's link.

A slight tweak...

tblStore
StoreCode
StoreLocation
etc.

tblItem
ItemCode
ItemType
Maufacturer
SKU
Description

Comment:
Item# may get you into trouble since "#" is used for encapsulating dates

tblStoreItem
StoreCode
ItemCode
RevisionDate
QuantityOnHand
Cost

BTW Cordury2, Your approach is excellent...
I want the end user to be able to enter a store # and it db will display ALL radios for that store.

Knowing what you want goes a long way in designing your database. Food for thought. Will you want to see how many radios you have at all sotres? Will each store have different prices? Is the primary focus of the database to track price for an item, track inventory, etc?

Richard