Newbie mySQL questions

[KennyR]

Hi all...the title of this says it all so I'll get to the point. I am struggling with the concept of database design. What I am having a hard time wrapping my head around is allowing multiple users to store and retrieve data from a single table. I have a web server from GoDaddy.com and have set a simple table up with 3 fields as follows:

1) User ID (auto Increment)
2 Product Name (varChar 255)
3) UPC (VarChar 255)
*Nothing more complex than this....

What confuses me is this....If multiple people are using my program and add data to this table and later retrieve it, how can the data that is placed by "User1" be unique from "User2" and only the data that was put in the table by User1 is returned to User1. Stupid question I know, but I am unclear on this....

*Note; what I mean by multiple users is separate users not associated with each other on different computers or smart devices...

Thanks
Kenny

[sturgis]

It won't work the way you have it set up. Since user id is an autoincrement, you can most likely not have multiple records with the same user id (it sounds like you Do want to have multiple records for 1 id)

It would probably be better to have 2 tables for this

a user table..

userId autoincrement (primary key)
and whatever other identifying data you need (like a user friendly nickname or whatever)

Then a second table with..

userId (not auto increment, not unique)
Product Name
UPC


When the user creates their acct, populate the first table.

When they log in from then on user their userId to add data to the 2nd table. That way you can easily do a select from the second table where userid = theirUserId

Or you could do it all with a single table and either have no primary key and just set userID to not autoincrement, and generate new ids yourself. then same deal. the where clause would look for the non-unique records where userid=theirUserId

[KennyR]

Thank you for the help! But quick question....do I need to make a foreign key on the second table to reference the UserID of the first table?

[sturgis]

Thank you for the help! But quick question....do I need to make a foreign key on the second table to reference the UserID of the first table?

Yeah. Notice the fields I listed for the 2 tables

In the user table is userId as the primary key.

In the second table is userid not auto increment, not unique which acts as the foreign key to the user table.

[KennyR]

Thank you Sturgis for the help...What I decided to go with was a single table that is associated with a registration/login card that assigns an ID in a column (auto inc) for the userID. Then in order to retrieve individual records for that user, I do a query to find out the users individual ID, based on their username, and store that ID in an invisible field for my next query. Now that they are logged into the database, each time I do a query, I filter the results based on their ID. This is prob the worst way of doing this, but I am still confused on how one table can pull data from another just based solely on the Primary key of the first table. I assume this is done with a "Join" command, but I am not that far along in my knowledge of databases to be proficient. I am reading a lot of stuff out there on the web and am still waiting for that "AH HA!" moment....

On another topic, I have a quick question about loops when it comes to retrieving data....I have been toying around with a repeat command that queries the database on a specific time interval...So lets say I have orders coming in to a database and I want to know within 60 seconds or less that a new item has been added to the DB....Should this be done with a repeat command? I have tried this and it seems to work for Mac/Windows platform, but the program occasionally hangs or freezes.... (pseudo code below)

on CheckDB
connect to DB
repeat forever
query the DB and place results in a field
wait for 60 seconds with messages
end repeat
end CheckDB

Is this totally out of the question and if so, is there a better way of checking the DB for changes on a regular basis?

Thanks for the help guys....I am very interested in what everyone thinks...