[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