Storing and Retrieving Multiple Objects From 1 ...

[courtjester]

Storing and Retrieving Multiple Objects From 1 Feild. I need to know how this can be done. What I am talking about is instead of making 1000 different empty fields in my database, just make one and make it where when a user selects an item it adds to that field.

FE- 1,2,4 Where 1 is its own item 2 is its own item and 4 is its own item. How would I go about doing this?

[Rob]

Can you explain exactly what you are trying to do?

What is the database for. What do the fields represent?

I think there will be a much better way that using 1000 fields (OMG)

[courtjester]

I'm trying to keep track of my user's using a database instead of SessionVariables.. I want to be able to see what items they have chosen to buy or what not As well as how many of each product they are going to buy. I was told to set up my database where I put my products in a seperate table from my user's so that when A user wants to buy something I just Use the ID Number off of that product and put it in a Field Called Buy. But This only works if the user wants to buy a single item. So I was wondering using that logic could I just putt multiple records into that field seperating them with a comma. And how would this be possible?

[Rob]

yes, you can do this.

The field however would have to be one of the text(string) type fields.

Then, you can just throw multiple values in there seperated by commas.

When pulling them out again in ASP, use the Split(MyString,",") function to return
an array of product numbers.

Again, I dont really think this is the right approach.

I think you would be better storing orders, in two tables. The tables (name in bold) would go something like this:-

tblTransaction
TransID
tr_CustId
tr_Date
tr_Price

tblTransItems
TransItemID
ti_TransID
ti_ProductID
ti_Quantity

Obviously your tables will likely contain more fields, but this approach will make it far easier for you in the long run, and would be the 'normalised' approach to constructing your database.

Reporting will be easier too.

Hope you see the logic here.

[courtjester]

Why the two tables? I don't understand why I'd use two tables for this. If you say it's the best approach Then I am most certainly sure that it must be cause you know what your talking about.

[D3mon]

The answer lies in the relationships between the three tables - tblCustomers, tblTransaction
, tblTransItems. For simplicity, let's rename those to:

tblCustomers
tblBasket
tblBasketItems

tblCustomers lists all the customers (and possibly all their details) and each customer has their own (unique) customerID. The CustomerID field is usually set to auto-number - that is, every new customer that is added to the table is automatically given the next available ID number by the DB. This field is also set as a 'Primary Key'. Let's use this as a simple exmaple table:

CustomerID | CustFirstName

1 | Anna
2 | Dave
3 | Clive
4 | Bob

Here, Anna has a CustomerID of 1. If I add another customer to the table, Ian, he will be automatically assigned a CustomerID of 5 (the next available/unused ID number.)

Every Customer will have at least one shopping basket (but some might want more than one), so we'll create a new table of shopping baskets tblBasket. Again, Each one will have it's own auto-generated ID (called BasketID) as and when a customer starts the shopping process. So we know who's basket is whose, we add the associated Customer ID to tblBasket. Let's say Dave is the first person to start shopping:

BasketID | CustomerID
1 | 2

The Basket Table now shows that it has one basket and it can use the CustomerID (as what's called a Foreign Key) to compare with the Primary Key in tblCustomers and find that this is Dave's basket (CustomerID 2).
With the Primary/Foreign Key relationship set up this way, Dave could theoretically have as many baskets as he wants, as in each case, we just create a new basket, it gets a new Basket ID in tblBaskets, and is set as customerID 2 again:

BasketID | CustomerID
1 | 2
2 | 2

This is know as a one-to-many relationship, since one customer could have many baskets. Although you may not want this Feature right away, it is best practice to build this flexibility into the DB system early, so that it's much easier to facilitate the feature in the future.

Similarly now, Customers will almost certainly want the ability to add more than one product into a basket, so we create a new table, tblBasketItems, with the same relationship structure, but this time, between the Basket and the items in it:

BasketItemID | BasketID | ProductName
1 | 1 | Apple
2 | 1 | Orange
3 | 1 | Banana

tblBasketItems has a Primary Key of BasketItemID, a Foreign Key of BasketID (relating directly to tblBaskets) and a description of the item. Note that we don't need to add the CustomerID to tblBasketItems because we can find that out based on the relationships we have already built:

we can refer to the fields in these tables with the following notation:

[tablename].[fieldname]

So...

tblBasketItems.BasketItemID 3 is a 'Banana'
tblBasketItems.BasketID is 1 (where tblBasketItems.BasketItemID is 3)
tblBaskets.CustomerID 1 is (where tblBaskets.BasketID is 1)
tblCustomers.CustName = 'Dave' (where tblBaskets.CustomerID 1)

In this way we have stored the minimum information (with no duplicate content stored), but created the most flexibility this simple system might ever need.

[courtjester]

Wow. That's Awesome. Thanks for the Explination. I actually get it now. I'm going to start working on this!

[D3mon]

No probs. Maybe I should post this as an article.

[benbacardi]

that would be good... an <u>in detail</u> article about shopping carts etc in ASP would be very helpful...

[Rob]

D3mon... I couldnt have put it better myself.

I would love an article on this... not nessesarily ASP focused (you can if you like)... it could be SQL generic for the SQL article section.

Nice reply.