Another Database design question?
Posted: Wed Aug 30, 2006 5:34 pm
Ok sorry for the way i have to ask this question. I wish i could just post the code and URL for the site but it would unfortunately be a breech of contract and im liable for a substantial amount of money if i do that.
So here goes:
I have a rather large subscriber marketing profile which currently has 308 fields. Well its broken into seperate forms but it all gets posted to one MySQL table.
I have a sponsor side for business clients that im trying to match these profiles too. By staying extremely flexible on this side i have come to a dilema as to how to match the two together. I dont want to inconvenience the sponsors by asking them to enter all 308 fields everytime they submit a new campaign. So i have broken it into about 10 major categories. They can fill out all 10 for more targeted resultrs or just 1 if they like reaching more people.
Im trying to also limit server load as far as the amount of information the database is needing to spit out on each querry. So ive come to some people smarter than i to try and figure out how to set this up.
1. Should i break the sponsor campaign table into 10 seperate tables corresponding with the Categories?
If i do it this way how hard will it be to match these 10 seperate tables to the large subscriber table if a sponsor was to fill out all 10 categories?
Im not exactly sure how i would querry 10 tables and sort and match the results with a larger table. Not to mention both are stored in seperate Databases just encase one was to crash i wouldnt loose everything.
2. Should both tables get broken into the 10 major categories and just be linked with the subscriber / sponsor unique ID numbers?
This way i only need to sort and match the corresponding tables (eg sports, leisure, etc.)
But i think having 10 seperate tables linked to each user would be a pain in the ass if the number of users grows to say 100k.
3. I have no idea what to do with this concept my client wants me to do. So please help me.
Anyways if this makes no sense at all ill try to elaborate as best i can without getting in troulbe.
p.s. anyone know of a good way of generating random unique customer id numbers. I am currently using the session id assigned to them but i dont like that method what so ever.
thanks for all the help.
So here goes:
I have a rather large subscriber marketing profile which currently has 308 fields. Well its broken into seperate forms but it all gets posted to one MySQL table.
I have a sponsor side for business clients that im trying to match these profiles too. By staying extremely flexible on this side i have come to a dilema as to how to match the two together. I dont want to inconvenience the sponsors by asking them to enter all 308 fields everytime they submit a new campaign. So i have broken it into about 10 major categories. They can fill out all 10 for more targeted resultrs or just 1 if they like reaching more people.
Im trying to also limit server load as far as the amount of information the database is needing to spit out on each querry. So ive come to some people smarter than i to try and figure out how to set this up.
1. Should i break the sponsor campaign table into 10 seperate tables corresponding with the Categories?
If i do it this way how hard will it be to match these 10 seperate tables to the large subscriber table if a sponsor was to fill out all 10 categories?
Im not exactly sure how i would querry 10 tables and sort and match the results with a larger table. Not to mention both are stored in seperate Databases just encase one was to crash i wouldnt loose everything.
2. Should both tables get broken into the 10 major categories and just be linked with the subscriber / sponsor unique ID numbers?
This way i only need to sort and match the corresponding tables (eg sports, leisure, etc.)
But i think having 10 seperate tables linked to each user would be a pain in the ass if the number of users grows to say 100k.
3. I have no idea what to do with this concept my client wants me to do. So please help me.
Anyways if this makes no sense at all ill try to elaborate as best i can without getting in troulbe.
p.s. anyone know of a good way of generating random unique customer id numbers. I am currently using the session id assigned to them but i dont like that method what so ever.
thanks for all the help.