close ad
WARNING PC USERS: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

setup products table mysql database

Thread began 1/15/2010 12:33 pm by mr hankey | Last modified 1/25/2010 7:34 pm by Eric Mittman | 962 views | 9 replies |

mr hankey

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

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

mr hankey

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

Sign in to reply to this post

mr hankey

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

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

mr hankey

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

Attached Files
test.txt
Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

mr hankey

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

thanks again

Sign in to reply to this post

mr hankey

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

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...