Linking multiple Tables for temporary Data Retrieval

[doomed2020]

Hi there,

I am working on my College project, making an eShop using PHP and MySQL , most parts of my website are competed , there are some points which i am unable to manage.

My main problem now is to link two Tables of MySQL Database in such a way that by accessing 1st table i can get data from the 2nd table too , without writing a lot of MySQL and PHP coding , i know it is possible , but i am unable to do it properly!

This is what i am trying to accomplish:

=>I have three tables in MySQL, "products" ,"tempuser" and "reguser".

=>"products" includes Three main Rows, [id], [pid] and [brand]
-->[id] is *primary and *Auto Incremented (for DB use only)
-->[pid] is *Unique and is the Product ID(for product Identification and fetching)
-->[brand] includes the name of the products i am using(e.g Asus , Sony , Toshiba etc)


=>"tempuser" includes temporary users who have not yet signed in , but still interested in buying products.
-->it includes temporary information about users like , IP address , browser , Referrer etc.
-->it also includes the products, the user has added to cart(Temporary) or added to his wish list, but i don't want to INSERT the same data that already exists in "products" TABLE , so i just want to link the information to the "products" TABLE .

I could use DESIGNER located in PHP MyADMIN, but this only works when the two tables have Fixed number of rows , but in my case number of ROWS may Increase/Decrease depending upon the number if items the user has selected/Added to Cart.

I am also not sure what should I insert in the "tempuser", if a user adds two or more Items of the same brand to his Cart, or if he adds two or more Items of two or more products in his cart.
I mean without making a lot of rows , allotting Single Row for the Single Brand , which should include Quantity,pid and brand name in such a way that it can be matched with "products" table in case if a user clicks on the "Details of the product" button during the Checkout process.

I know my information is bit confusing , that is why i am sharing it with you , so that you could help me in resolving this small but Confusing issue.

Please explain its solution in light of your Experience and guide me to finish my project. This is not the Only issue in my website , but this is exclusively related MySQL .

If you need further information from me , please post here.

Thanks

[guelphdad]

much easier for us to assist you if you follow these steps:

1) provide CREATE TABLE statements
2) provide INSERT STATEMENTS to fill the data
3) provide expected results from the query.

[Old Pedant]

=>"products" includes Three main Rows, [id], [pid] and [brand]

No. "products" includes three main *COLUMNS* (or "Fields"--the two terms tend to be used interchangeably).

Rows is used by many people as a synonym for RECORD.

Rows and Columns tend to be used by people who are used to working with spreadsheets.

Just to avoid confusion (and to avoid drawing too many parallels with spreadsheets!) I prefer using "Records" and "Fields".

But either way is understandable. But confusing rows and columns is not.

-->[id] is *primary and *Auto Incremented (for DB use only)
-->[pid] is *Unique and is the Product ID(for product Identification and fetching)

There is really no need to have the id field. If you have another field which is guaranteed unique (pid, in this table) it makes a fine primary key. If anything, having two unique keys can only lead to confusion. I'd be sorely tempted to kill off the id field, were I you.

************

Overall, I think your DB design is plain flat wrong.

You should have only *ONE RECORD PER USER* in the tempuser and reguser tables. ONE. And all it holds is the basic information *ABOUT THE USER*.

Actually, I see no reason for the tempuser table. I would get rid of it and just add one more field to reguser: IsPermanent. Set it to false for a temporary user, set it to true for a permanent one. Simplifies the heck out of the DB management.

*THEN* you need *OTHER* tables:

-- Orders : Each new order from a user (temp or not temp!) needs one record here
-- OrderItems : Each new item in a single order goes here (that is, these are the shopping cart records)
-- WishListItems : Each new wish list item goes here.

Possible starting schema:

CREATE TABLE regusers (
    userid INT AUTO_INCREMENT PRIMARY KEY,
    isPermanent BOOLEAN,
    name ...
    address   ...
);

CREATE TABLE orders (
    orderid INT AUTO_INCREMENT PRIMARY KEY,
    userid INT REFERENCES regusers(userid),
    orderDate DATETIME,
    shippingCost DECIMAL(12,2),
    ... etc. ...
);

CREATE TABLE orderitems (
    itemid INT AUTO_INCREMENT PRIMARY KEY,
    orderid INT REFERENCES orders(orderid),
    pid INT REFERENCES products(pid),
    quantity INT,
    color VARCHAR(),
    ... etc. ...
);

CREATE TABLE wishlistitems (
    wishid INT AUTO_INCREMENT PRIMARY KEY,
    userid INT REFERENCES regusers(userid),
    pid INT REFERENCES products(pid),
    priority INT, 
    whenAdded DATETIME,
    ... etc. ...
);

[Old Pedant]

allotting Single Row for the Single Brand , which should include Quantity,pid and brand name in such a way that it can be matched with "products" table

As you can see, this is what my OrderItems table is for. *BUT* you should *NEVER* put both pid and brand name into the OrderItems table. *ONLY* use the pid, as only it is guaranteed unique.

This is all part of the basic process of NORMALIZATION.

[doomed2020]

UnRESOLVED

much easier for us to assist you if you follow these steps:

1) provide CREATE TABLE statements
2) provide INSERT STATEMENTS to fill the data
3) provide expected results from the query.

I have no trouble regarding the CODE , i can create TABLES, COLUMNS, ROWS etc , its is the Structuring Design of my Database , which i am little confused.

RESOLVED

No. "products" includes three main *COLUMNS* (or "Fields"--the two terms tend to be used interchangeably).

yap , my mistake , i forgot , these are Columns , not Rows. Thanks for the Correction.

RESOLVED

There is really no need to have the id field. If you have another field which is guaranteed unique (pid, in this table) it makes a fine primary key

Done , Removed the [id] COLUMN , i thought it would be wise to have an Auto-Incremented INT figure in the table , as my [pid] contains some alphabets as well , hard to apply Auto-Increment over it, in case a New item of Same brand is added to the Database.

UnRESOLVED

You should have only *ONE RECORD PER USER* in the tempuser and reguser tables. ONE. And all it holds is the basic information *ABOUT THE USER*.

Yes i have one "reguser" TABLE for this purpose, but its sole purpose is to store the basic and private info of the User (e.g. Name, Address, phone, email, password(md5), Credit Card Info , Registration date , log in date etc.) but that TABLE only belongs to those users who have signed Up and Activated their Accounts Successfully , but as we know most of the users just Visit to Check the Functions,capability, Products, features, reviews , price on the website (so they are Anonymous to us) so there should be a TABLE to track their Activities,selection, clicks etc , so in case if they log in later , that particular data (their added to Cart Items) can be Copied to their REGISTERED USERS table.

Let me assure u that my "tempuser" TABLE is not useless , will contain important but Temporary User specific information ,which can be transferred to his/her "reguser" table if he/she logs in/Signs up in future , it will be hard to track this info in "reguser" TABLE when most of the users are unknown to us.

UnRESOLVED

*THEN* you need *OTHER* tables:

-- Orders : Each new order from a user (temp or not temp!) needs one record here
-- OrderItems : Each new item in a single order goes here (that is, these are the shopping cart records)
-- WishListItems : Each new wish list item goes here.

I can make these TABLES , no Problem , but how to relate them with the users who have not yet signed in (u can still shop and add to Cart, on most websites before CHECKOUT e.g amazon.com) ?

CREATE TABLE orderitems (
    itemid INT AUTO_INCREMENT PRIMARY KEY,
    orderid INT REFERENCES orders(orderid),
    pid INT REFERENCES products(pid),
// Reference to tempuser(userid) ?
    quantity INT,
    color VARCHAR(),
    ... etc. ...
);

RESOLVED

*ONLY* use the pid, as only it is guaranteed unique.

i'll make sure to use [pid] only for product Identification purpose at any stage.


I hope you understand my question , most of question have been answered by you(Old Pedant) , thanks for that , but a little help is still needed , so please give a small amount of your time to my problem .

Thanks

[Old Pedant]

I can make these TABLES , no Problem , but how to relate them with the users who have not yet signed in (u can still shop and add to Cart, on most websites before CHECKOUT e.g amazon.com) ?

Well, I still say you'd be better off in the long run consolidating your reguser and tempuser tables. Even if some of the fields are only used by one or the other state (isRegistered being true or false), it's better than two tables. Mostly because of the need to relate a primary key in this table (or these tables) to the orders table.

This is a dilemma faced by many DB designers. There are various ugly solutions.

You could do

CREATE TABLE orders (
    orderid INT AUTO_INCREMENT PRIMARY KEY,
    reguserid INT NULL REFERENCES regusers(userid),
    tempuserid INT NULL REFERENCES tempusers(tempid),
    orderDate DATETIME,
    shippingCost DECIMAL(12,2),
    ... etc. ...
);

And then ensure that you never have both reguserid and tempuserid non-null in the same record.

It works, but it complicates your later work.

In order to display an order, you typically end up doing something like this:

SELECT IFNULL(regusers.username,tempusers.username) AS name, 
       IFNULL(regusers.email,tempusers.email) AS emailaddress,            
       ...
FROM orders
LEFT JOIN regusers ON orders.reguserid = regusers.userid
LEFT JOIN tempusers ON orders.tempuserid = tempusers.tempid

UGH!

Truly and honestly, most sites opt for the solution I gave you.

It may increase the record size of you users table a little bit, but it's way worth it in the long run.

[Old Pedant]

If you are worried about cleaning the table of tempusers, that's not hard.

You can just use a CRON job or similar to go out and remove all tempusers that have had no activitity in, say, the last day (or hour or whatever you choose). That is, if a tempuser hasn't been converted to are reguser, that must mean that he/she didn't finalize the order.

[Old Pedant]

And notice that if you still decide to go with the separate tables, at least the only affects the ORDERS table. The orderitems depend only on orders.orderid.

And I would assume you would not bother keeping a wish list for unregistered users.