I want to create a table having fields username, password and products.
For a single user no of products is not fixed, it varies from 1 to 30.
My first column is username, 2nd is password and in third column i want to add products.
I don't want to make separate columns like product1, product2, product3 because no of product is not fixed.
Is it any alternatives or something like arrays in MySQL that can hold multiple products and later i can chose any of the products by sql query for a user.

Thanks in advance for any kind of suggestion.

    normalize the table so that an individual user can have an unlimited number of products, if you break apart the username, password and products table you can do so . You can do this with either 2 or three tables, depending on whether the same product can have multiple users

    Table 1 - users
    User Id | User Name | Password
    1 | Bill | mypassword
    2 | Bob | kitty

    Table 2- products - 1 user per product
    Product Id | User Id | Product Title
    1 | 1 | Bill's Product 1
    2 | 1 | Bill's Product 2
    3 | 1 | Bill's Product 3
    4 | 1 | Bill's Product 4
    5 | 2 | Bob's Product 1
    6 | 2 | Bob's Product 2

    OR

    if a product can have multiple users

    Table 2 - products
    Product Id | Product Title
    1 | Canned Eggs
    2 | fruit loops
    3 | kittens
    4 | scotch tape

    Table 3 - Associate Products with Users
    User Id | Product Id
    1 | 2 --- Makes Bill have product fruit loops
    2 | 2 --- Makes Bob have product fruit loops
    1 | 1 --- Makes Bill have product canned eggs
    1 | 3 --- Makes Bill have product kitten
    2 | 4 --- Makes Bob have product scotch tape

      A friend recommended me to use spaces to separate products and
      then use $arr = explode(" ", $res);
      to get the different products,
      Which method is the best practice, I am beginner so i want to go on right track so in future i don't have to learn basics again.

        well, it really all depends on your needs,
        to address your question, when ever you are going to use explode, make sure your dividing by a character t hat you wont be using in the terms.

        For example, exploding with spaces will work for "apple banana orange"
        but for "Texas Nebraska New Jersey" it will output
        Texas
        Nebraska
        New
        Jersey

        so seperating by commas, semi colons, colons or ever character sets you make up is a better idea because that way you dont compramise your data, example
        Texas, Nebraska, New Jersey, Ohio ::: would work
        you can even do

        $Seperator=":X:"; //// something uncommon that ownt be used in your data
        $String="New Jersey:X:New York, New York:X:foo();:X:292:X:";
        $Arr=explode($Seperator, $String);
        will output:
        $Arr[0]="New Jersey";
        $Arr[1]="New York, New York";
        $Arr[2]="foo();"
        $Arr[3]="292";
        $Arr[4]="";

        This method is by no means practical in terms of DB normalization, structure or optimization, but in the beginning making it work, and making it work well (right) are worlds apart, so you do it the method that you think you can handle.

        Again, in terms of db storage, adding the same information over and over is impractical and not recommended, it becomes less manageable over time and makes the database bigger then it needs to be

        So lets say every user can have multiple products, on a table where it all in one row a single user's entry can look like

        Table 1:

        Entry Id	|	User 	| 	Password 	| 	Product Title 
	1		|	Billy	| 	Goat		|	Bicycle
	2		|	Billy	| 	Goat		|	Car
	3		|	Billy	| 	Goat		|	Train
	4		|	Billy	| 	Goat		|	Goat Coat
	4		|	Billy	| 	Goat		|	Sox

        A: making a log in would be very unreliable since in some instance there could be inconsistant data such as a misspelling of Goat to Gaot, so now the user can login with either Goat or Gaot
        So username, passwords by rule of thumb should be in their own table with unique keys to make sure no usernames are alike.

        😎 the repetition of Billy : Goat means that every time you wanna update the username or password you have to update all those fields

        C) Like anything, a database's size is proportional to the data in it.
        the value "Billy" being repeated will take up more space then an id "25"
        In this case the differance isnt great, but when your dealing with large DB's, which you probably wont be for some time, that differance can be crucial to the DB

          I think the first option of making two separate table "login" and products"
          By doing that I will be able to learn normalization also that can be very useful in handling bigger database.
          I am newbie so i dont mind learning new things.
          Thanks again for your help and valuable suggestions.

            Write a Reply...