lnking tables

[andydeans]

Hi,

Fairly new to MYSQL, still learning.

I have create basic web databases before but the project i am working on just now is slightly more complex than before and need some advice.

i have had some help qith queries which has been great however i need some advice from you guys.

Basically in the database there is multiple product tables as the criteria in the tables are different from each other.

In my app i need to display info for these products, whether that be how many products sold in total or more complicated data like how much due in this week.

what are your guys thoughts on me using a product table that has a relationhip between all the 25 indivdual product tables and the users table to get data onto my web app?

i would appreciate any advice.

thank you

[griswolf]

Hi,

Fairly new to MYSQL, still learning.

I have create basic web databases before but the project i am working on just now is slightly more complex than before and need some advice.

i have had some help with queries which has been great however i need some advice from you guys.

Basically in the database there is multiple product tables as the criteria in the tables are different from each other.

In my app i need to display info for these products, whether that be how many products sold in total or more complicated data like how much due in this week.

what are your guys thoughts on me using a product table that has a relationship between all the 25 individual product tables and the users table to get data onto my web app?

i would appreciate any advice.

thank you

If i understand your question, you are suggesting that you will have a table users , some tables product_N and an association table users_products where each row contains a user_id and one or more product_id . That is a good, standard technique.

The only issue is whether you have multiple product tables or one product table with multiple product types. With single table, the association table has just the two columns. With multiple tables, you will probably be best served by having one productN_id column per product table.

[andydeans]

Hi thanks so much for the reply.

So you reckon it is fine to use a linked table?

I read somewhere not to have repetitive data?

Basically the multiple product tables have in them each a "ProductNameID" so that could be "CarInsuranceID" or "HouseInsuranceID"

Each table has slightly different criteria but the do have a "Commission" column to work out how much each sales user has made.

I was thinking i could link the commission and productnameid along with the userid from users table to one table for easier querying? Do you think that would be fine then?

Thanks again, sorry just want to be sure as you get so many different answers from different people.

[griswolf]

Linking or association tables are just fine: You associate rows from two different tables by having the row_id for each table in the association table.

Yes, you want each datum in its place and a (single) place for every datum. Look for "Normal form" in your references.

In order to use an association table, you need to know in advance the names of the associated tables so you can write queries (I suppose you could generate queries on the fly, but that isn't a usual technique). So you will either need as many association tables as there are product tables; or you will need as many columns in the association table as there are product tables; or you will need a somewhat more generic product table that can handle all of the various products. Which of those choices is best depends on your particular circumstance. When in doubt, I try to predict what is going to happen 'next year' and design so the likely maintenance effort will be minimized. Of course, predicting the future is difficult...

[andydeans]

Thanks that helps.

I know the table names of the tables i would need to associate to and all the columns so you think i can just go ahead and go for it?

thanks again for your help