Accessing an entire database column

[littlegreenmen]

This is the last piece of my website puzzle and any help finishing it would be
most appreciated. Basically, my website has a login page, then a form for the
user to fillout which is then inserted into a database. I have the login
working correctly, as well as the form, with one exception. I have 3 Access
databases. 1 is the username/password database, the 2nd is the form where the
information from the form website is stored, and the last one is information
that relates to who logs in (will call it the store database). It's this
database I am having trouble referencing. In the login database there are 3
columns. 1 for username, password, and the name of the store where they work.
In the store database, I have several columns, each named after the different
stores that the users could work in. For example, user 1 works in store A;
user 2 works in store C, user 3 works in store B, user 4 works in store B, etc.
Under each of the store headings in the store database is differenet
information that I need to import into one of my menu selections on the form
website. My question is, how to I reference an entire row from the store
database based on who logs in? In other words, if user 2 logs in how do I get
all the information in the C column of the store database? In the 3rd column
of the login database is the name of the store where they work, A, B, C, etc.
This name also corresponds to the column name in the store database. The names
of the columns would be A, B, C. Each with differenet information underneath.
I hope I explained my dilema clearly enough. Thank you for any and all help.

[Dan Bracuk]

Two suggestions:
1. Just use one database, not 3. Normalize it.
2. Don't name columns after the stores, set up a many-to-many relationship
instead.

If you don't understand those suggestions as written, it's in your best
interest to learn the basics of data modelling.

[mxstu]

The description of your database structure is a bit confusing. I assume when
you say you have (3)
separate databases you actually mean (3) separate tables within (1) database?

It sounds as if the "stores" table is structured like this:

Table Columns:
=============================
StoreID (unique record id)
StoreA
StoreB
StoreC

This doesn't seem like a very flexible structure. I'm not sure what type of
information are you storing in each
of the "Store" columns? Is there a reason you are using columns as opposed to
a more normalized row structure like this ?

StoreID | Store Name | SomeProperty | SomeOtherProperty
================================================== =======
1 | Store A | 'Property A' | 'Other Property A'
2 | Store B | 'Property B' | 'Other Property B'
3 | Store C | 'Property C' | 'Other Property C'

[littlegreenmen]

I apologize. I did mean 3 tables within 1 database. You are correct in
understanding how I set up the Store table. If i Set it up like your 2nd
example:

ID | Store Name | Inventory 1 | Inventory 2 | Inventory 3
===============================================
1 | A | Item 1 | Item 2 |
Item 3
2 | B | Item1 | Item 2 |
3 | C | Item 1 |
| Item 3

There is an example of the new way to set up my table. How would I reference
the entire row for Store A, B, or C based off the returned value from the login
table? Thank you very much for your help.

[mxstu]

If all of the colums contain "inventory", then I think you should normalize it
further by putting "inventory" in a separate table.

Store
===============
StoreID
StoreName


Inventory
===============
InventoryID
InventoryName

StoreInventory
======================
StoreInventory (Unique record id)
StoreID
InventoryID

So the data would look like this

StoreInventoryID | StoreID | InventoryID
===============================================
1 | 1 | 1 <--- Store A, Item 1
1 | 1 | 2 <--- Store A, Item 2
1 | 1 | 3 <--- Store A, Item 3
1 | 2 | 1 <--- Store B, Item 1
1 | 2 | 2 <--- Store B, Item 2
...etc ...

Then to get the inventory for a specific store, just run a select statement

--- not tested
SELECT i.InventoryID, i.InventoryName
FROM StoreInventory si INNER JOIN Inventory i ON si.InventoryID =
i.InventoryID
WHERE si.StoreID = #someStoreID#

[littlegreenmen]

what i have so far is:

SELECT tblUser.Store
FROM tblUser, tblStore
WHERE tblUser.Username = '#Session.MM_Username#'
and tblUser.Store = tblDealership.Store

This returns the name of the store just fine. How would I go about getting it
to display the entire row that realtes to the store name? I apologize if I'm
not coding this the most efficient way. I am rather new to this whole thing,
but I greatly appreciate your help.

[mxstu]

No problem. If this is also a new database, it is easier to set it up
correctly now then make changes later ;-)

>How would I go about getting it to display the entire row that realtes to the

store name?

So, are you using the separate table for inventory (as in my last post) or are
you talking about the orignal table structure you posted?

[littlegreenmen]

I think I'm talking about doing it where it is all one table, like we were
talking baout before. I think that will be easier to maintain in the long run
as there could be 100 stores and 10-15 items per store. Let's say I have 1000
employees that work at 1 of those 100 stores. When they log in I need the drop
down menu to display only the products that store carries. It might be 5
items, it might be 1 items. It varies depending on the store. If it is
possible to do it as 1table for the store and 1 for the users I would like to
go that route, but if I have to do it as separate tables then that is what I
will have to do. But, if I go the separate table route, how do I get it to
access tblX where X=the name of the store that corresponds to the username in
the login table?

[mxstu]

>When they log in I need the drop down menu to display only the products

>that store carries. It might be 5 items, it might be 1 items. It varies

depending

> on the store.

Separate tables may sound more complicated, but it is a better database design
and IMO will make your queries much easier than if you were using the single
table design you first described.

Assuming the "user" table contains the "storeID", you would use a simple JOIN
on the tables to retrieve the items carried by the selected user's store.

--- not tested ----
SELECT i.InventoryID, i.InventoryName
FROM Users u INNER JOIN StoreInventory si ON u.StoreID = si.StoreID
INNER JOIN Inventory i ON si.InventoryID = i.InventoryID
WHERE u.UserID = #someUserID#

* "Products" is probably a better name than "Inventory" for your table/columns

SAMPLE DATA:

USER
------------------------------
UserID | UserName | StoreID
------------------------------
1 | 'jsmith' | 2
------------------------------


STORE
------------------------------
StoreID | StoreName
------------------------------
1 | Store A
------------------------------
2 | Store B
------------------------------
3 | Store C
------------------------------

INVENTORY
------------------------------
InventoryID | InventoryName
------------------------------
1 | Item 1
------------------------------
2 | Item 2
------------------------------

StoreInventoryID | StoreID | InventoryID
--------------------------------------------
1 | 2 | 1 <--- Store B, Item 1
--------------------------------------------
2 | 2 | 2 <--- Store B, Item 2
--------------------------------------------

[littlegreenmen]

After messing around with it, I agree that multiple tables would be the better
route to follow. My only question pertains to the inventory table. Are you
saying that I put all items in this table that would show up in all the stores?
In other words, let's say I have 3 stores (A-C) in the stores table. Each
store carries 3 different items, for a total of 9 items (1-9). Would my
inventory table be set up as follows:

Inventory | InventoryName
---------------------------------
1 | Item 1
2 | Item 2
3 | Item 3
4 | Item 4
5 | Item 5
6 | Item 6
7 | Item 7
8 | Item 8
9 | Item 9

Let's say Items 1-3 pertain to store A, 4-6 to store B, and 7-9 to store C.
Is this still how I would set it up, or would I want a table for Store A that
contains items 1-3, a table for Store B with items 4-6, and a table for Store C
with items 7-9? Thank you again for your help.

[Dan Bracuk]

I'd do inventory like this:

StoreID|ItemId|Quantity

[mxstu]

littlegreenmen,

Dan raises a good question. I assumed from your earlier posts that what you
meant by "item1", "item2", etc, was the standard products carried by one or
more stores. Is that what you mean, or do you mean the number of products
currently in stock at one or more stores. The inventory is a little different
than a standard product list .

[littlegreenmen]

I appreciate the input, but for my site there isnt a quantity for the
inventory. I'm just trying to show what items different stores carry. Let me
see if I have this striaght. The first table is my login table which contains
username|password|storeID. Then I have the store name table which is
StoreID|StoreName. Then I have the item table, which is ItemID|ItemName.
Finally, I need a table to relate the store and item together. This is where
I'm confused. would I set it up; StoreID|ItemID? That way store A can have
items, 1,2,3,4, while store B has items 5,6,7,8,9 and store C has items
1,3,5,7,9. Would this table look like:

StoreID | ItemID
1 | 1
1 | 2
1 | 3
1 | 4
2 | 5
2 | 6
2 | 7
2 | 8
2 | 9
3 | 1
3 | 3
3 | 5
3 | 7
3 | 9

Then would I simply reference the StoreID|ItemID table from Dreamweaver? How
would I relate this to the StoreID from based off the username from the login
table? I don't remember if I mentioned it, but my database software is Access
2003. I really appreciate your patience with a newbie.

[mxstu]

Originally posted by: littlegreenmen
Would this table look like:

StoreID | ItemID
1 | 1
1 | 2
1 | 3
1 | 4
2 | 5
2 | 6
2 | 7
2 | 8
2 | 9
3 | 1
3 | 3
3 | 5
3 | 7
3 | 9

Then would I simply reference the StoreID|ItemID table from Dreamweaver? How
would I relate this to the StoreID from based off the username from the login
table? I don't remember if I mentioned it, but my database software is Access
2003. I really appreciate your patience with a newbie.

Yes, like that. Just make sure to add a unique ID (autonumber) column to that
table. So the final columns would be:
StoreInventoryID, StoreID, InventoryID

It is generally a good idea to have a numeric record ID column in all of your
tables.

To get the records, just JOIN the "login", "storeinventory" and "inventory"
tables together and filter on the userID

--- not tested ----
SELECT i.InventoryID, i.InventoryName
FROM Users u INNER JOIN StoreInventory si ON u.StoreID = si.StoreID
INNER JOIN Inventory i ON si.InventoryID = i.InventoryID
WHERE u.UserID = #someUserID#

Inventory is probably not a great name. Something like "Products" might be
better

[littlegreenmen]

Ok, I think I'm getting it. One last question: Why make 3 tables when I could
just make the one following:

StoreName | ItemName
Store A | Item 1
Store A | Item 2
Store A | Item 3
Store A | Item 4
Store B | Item 5
Store B | Item 6
Store B | Item 7
Store B | Item 8
Store B | Item 9
Store C | Item 1
Store C | Item 3
Store C | Item 5
Store C | Item 7
Store C | Item 9

Then If I add more stores I just add them to the bottom of the list. This way
I could see what store has what items without having to reference the numbers
back to their original tables, it's all right there on one table. And from
dreamweaver, I simply say show me all the items where StoreName of this table
equals StoreName of the login table. I guess my confusion lies in the fact
that they both look the same (to me) but this way only has the 1 table instead
of 3. I suppose I could see using multiple tables if there were more than the
2 variables, but in this case does it really make sense, and if so how?

[mxstu]

>Ok, I think I'm getting it. One last question: Why make 3 tables when I could

>just make the one following:

The reason for separate tables is
[url]http://www.google.com/search?hl=en&q=define%3Anormalization&btnG=Google+ Search[/url]
and the general rules for good database design. A badly designed database is
harder to maintain, more prone to contain errors and inconsistencies and
usually provides poor performance.

For example, let's say you did use a this table structure instead:

StoreName | ItemName
Store A | Item 1
Store A | Item 2
... etc...
Store C | Item 1
Store C | Item 3
Store C | Item 5

One day you are reviewing the products for "Store A" and you realize that
"Item 1" should really be "Item 1-A". So you update the table record for
"Store A"

Store A | Item 1-A

The other records in the table for "Item 1" will not automatically be updated.
Now your data is inconsistent and your queries may yield incorrect results
because the item is listed as "Item 1-A" for one store and "Item 1" for others.
If you used separate tables, you would simply update the "InventoryName" for a
single record in the inventory table and consistency would be maintained.

Another reason is basic performance. Queries on numeric fields are faster
than searches on varchar/text fields. So

[mxstu]

One more thing...

Just to make sure you understand the idea, the "Inventory" table should
contain the distinct items for all stores. So if (2) stores carried the
"ColdFusion MX 7 Web Application Construction Kit" book, your "Inventory" table
would only contain (1) record.

INVENTORY

---------------------------------------------------------
InventoryID | InventoryName
---------------------------------------------------------
13 | ColdFusion MX 7 Web Application Construction Kit
---------------------------------------------------------


STOREINVENTORY

---------------------------------------------------------
StoreInventoryID | StoreID | InventoryID
---------------------------------------------------------
26 | 1 | 13 <-- Store A, CFWACK Book
---------------------------------------------------------
27 | 2 | 13 <-- Store B, CFWACK Book
---------------------------------------------------------