setup products table mysql database

hi,

I need a hand with my app, i have users who register and can be assigned to multiple clients however i have multiple products which have different criteria so would i create a table for each product then create a product table also which relates to the individual products?

so say i have:

product1table
account-pk
name
amount

product2table
refnumber-pk
provider
amount

would i then have a product table which relates to these products individually and the clients table relating to individual products tables or just the product table which would say have:

productid
productname

??

hope someone can help me deisgn this as i have not got a great understanding of designing the db and creating relationships and when and where to sue these.

thanks again

I'm not following along with your example that you have posted. I think it may be best to start with some details about how this information will need to be used. If you can please post back with some more info about how the data will be used and what that experience will be like from the perspective of the user's interacting with it. With this information I think it would be a little easier to comment on the db structure that will best accommodate this.

thanks eric, i know it might not make much sense.

basically the app is a backend system that sales will store clients details. however the products that they sell range from car insurance to house insurance so not every product has the same fields in the db or the form that will be used to submit the data.

using dataassist, css form builder and security assist i want to be able to have sales go in log clients. so a salesman can have multiple clients, the client can have multiple products sold to them.

i just need some guidance on how to setup the db for the products as it is not same for every product will it be a table for each one?

any help would greatly be appreciated eric.

cheers mate

so a client can have multiple products sold to them but the criteria is different so do you just change the php pages headings of the form and the headings of the repeating table to display this on the page or is it best to have a table for each product as it is different criteria i.e. on might have start date of policy and the other might not have that field in its criteria.

thanks

How you will organize the clients and products will depend on what information you need to record for the products that the client has.

You might need a clients_products table that holds id's for the clients and corresponding id's for the type of product they have. You could then query the db for an individual clients info and products by joining the clients table with the clients_products table.

I think that there are a lot of parts to think about with this and you should spend some time detailing how the information would be stored and relate to see if it will fit your requirements.

If you go the route here then you may need at least two more tables, one for product types to relate the product type id to the product type name, then at least one more table for the type of product that it is to ensure that you can record the details for that type of product.

hi eric,

is this the correct setup for to create the relationship between products, users and clients to use data assist and others to display dynamic data properly?

attached txt file


thanks again

Ok, it looks like you have a users, products, and clients table. You have user relating to clients through a foreign key and the same for products. The thing that this structure does not account for is if the client has multiple products. This structure would be fine for holding a single product, but how would you record the details if the client had multiple products?

I think you could account for this if you had another column in the products table, if you added a client_product id column to this table you could use this as a foreign key to relate to the client directly. The products table would then have an entry for each product the client owned, it would contain the product id, the name of the product and the id of the client.

You could also leave the current structure in place and creating a lookup table for client_products. This table would have three columns, an auto incrementing id column for the primary key, a column for the client id, and a column for the product id.

I think if you add in this extra column or this lookup table you would be able to account for the multiple products per client.

thanks Eric, i am just getting my head around creating relationships between tables and now understand what you are saying.

thanks again

i take it that is the same for the users to clients as a user may have multiple clients? so create a column in clients table users_clientid and have a foreign key from userid in users table to this in the clients table?

thanks again

You are exactly rite about the relationship between the users and clients. Since user's can have more than one client, but clients do not have more than one user you should add in a user's column to the clients table. This will be a foreign key in the clients table that relates back to the users.

As for the clients and products I think it would be best to have a separate table that joins the clients and products.