SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict sorin21us's Avatar
    Join Date
    Mar 2009
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tables relations

    I have 3 tables:
    1.Company
    id int
    name varchar
    tel varchar
    email varchar
    manufacturer varchar
    producer varchar

    2.Products
    id int
    category varchar

    3.Subproducts
    id int
    subcategory varchar

    and I have my form

    Code HTML4Strict:
    <form action="">
    <input type="text" name="name">
    <input type="text" name="tel">
    <input type="text" name="email">
    <input type="checkbox" name="manufacturer">
    <input type="checkbox" name="producer">
    </form>

    When I insert the info for each company I will use the checkbox to show if that company is a producer or manufacturer for some products.
    So when I select the producer checkbox, another form will show up to select what products for that company, and after I select one or more categories, another form with subcategory will show up.

    My problem is that I don't know how to insert those things in tables. I mean that I don't know how to do the relations between tables, so when I will select a company to see if that company is producer, how many and what products has. of course a company can be producer and manufacturer, for the same products or not.

    How can I do the relations between tables? Or if my tables are wrong please tell me how can I modify each one.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I am understanding things right then the "Products" table is really a "ProductCategory" table and the "Subproducts" table is the table containing the products and each product is assigned to a product category.

    In that case each record in the Subproducts table needs to say what ProductCategory it belongs to. So you need an extra field productcategory_id to store the id of the entry in Products to which Subproducts belongs.

    In addition each Company has a number of products (Subproducts) so you need another table CompanyProducts which ties the two together. It would have fields company_id and subproduct_id. The table would contain one entry for each product of each company.

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As for the table structure, I think there is no need to have two fields named 'manufacturer' and 'producer'. You can one field like 'company_type' and you can insert values as 'M' (or) 'manufacturer' and 'P' (or) 'producer' for the respective types depending on which check box is checked.

    I think the table structure can be something like this.

    1.Company

    company_id int auto_increment
    name varchar
    tel varchar
    email varchar
    company_type varchar


    2.Products

    product_id int auto_increment
    company_id int
    category varchar

    3.Subproducts

    company_id int
    product_id int
    subcategory varchar

    And again it is better to store the Categories and sub categories as ids rather than the names of the categories itself being stored(That is what I feel you were going to do going by the field type for 'category' and 'subcategory' fields). May be the list of Catgories and the subcategories of each category wil have to form two separate tables.That will be much better.
    Thank You
    Known is a Drop, Unknown is an Ocean

  4. #4
    SitePoint Addict sorin21us's Avatar
    Join Date
    Mar 2009
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, but how do I insert data in 3 tables, in the same time ?

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sorin21us View Post
    Thank you, but how do I insert data in 3 tables, in the same time ?
    You cannot insert data into all the three tables at a time, atleast not in this case. Because, for each row inserted in the first table , we may have multiple rows inserted in the other two tables.

    You will actually have to take table by table. First the insert into the company table, which will be one row for each form submit. Insert into the other two tables will also be done by simple insert queries, but they will be called/referenced to be executed as many times as the number of categories or Sub categories that have been selected.

    But after the insert to the 'company' table is done, we need to have the last Inserted Id retrieved from it. Now, the Insert query to the second table will be in a loop in order to be executed as many number of categories selected. So, you will be passing the last inserted Id which will be the 'company_id' and the first category selected. Within the same loop you also have the subcategories insert Query(for third table) executed as many times as the Number of sub categories selected for the first category selected. This will demand a last Insert Id retrieval again.

    It should be something like this

    Code:
    {
       Call the Query that executes insert on 'company' table
       
       Get Last inserted Id (this will be company_id)
      
       for(int i=1;i<=No of Products selected;i++){
    
             Call query that executes insert on 'products' table 
    
             Get Last inserted Id (this will be product_id)
    
             for(int i=1;i<=No of Sub Products selected;i++){
    
                   Call query that executes insert on 'sub_products' table
    
             }
        }
    }
    Thank You
    Known is a Drop, Unknown is an Ocean

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    create table company
    ( company_id int auto_increment primary key
    , name varchar
    , tel varchar
    , email varchar
    )


    create table company_types
    ( company_id int not null
    , company_type varchar (32) not null
    , primary key (company_id, company_type)
    , reverse_ix (company_type, company_id)
    , constraints go here
    )


    create table products
    ( company_id int auto_increment
    , product_id int not null
    , category varchar(32)
    , primary key (company_id,product_id)
    )

    create table sub_products
    ( company_id int not null
    , product_id int not null
    , sub_cat varchar (32)
    , primary key (company_id, product_id)
    )

    Now you'll have to change that if a product can be in more than one category or more than one sub category because, then, they will be a one to many relationship and not, a one-to-one.

    read up on database normalisation

    bazz

  7. #7
    SitePoint Addict sorin21us's Avatar
    Join Date
    Mar 2009
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Someone told me that my logic with tables is not correct. I will tell you what I have and what I need.
    In database I should have products, but those products are not simple products. I will give you an example:

    1.Car->Hyunday->Elantra->GLS
    2.Car->Hyunday->Elantra->GT

    Like this are the products that I have to have in my database, but I'm not sure that my tables are correct.

    On the website I will have a form to insert a company(name, address, tel, web, etc). Under the form I have 3 checkboxes to select if the company is a Producer, Manufacturer, Client. I select what the company is and then the products will show up in <select>, to select what products this company has, if is producer, manufacturer pr client.
    Then I have to insert somewhere, in a diff table, the company id or name(I'm not sure witch one) with the products that I selected for that company, so when I will search a company to see that that company is a producer, manufacturer, client or a combination, and to see what products has.

    This is the link for you to try the script.

    If you have a better idea about how I build the tables please tell me.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •