Page 1 of 1

Another Database design question?

Posted: Wed Aug 30, 2006 5:34 pm
by sansoo
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.

Posted: Wed Aug 30, 2006 5:40 pm
by feyd
I think I'm going to need some explanation of every single field and why it's in a single table.

As for random customer id generation, take a look at the UUID RFC: http://www.ietf.org/rfc/rfc4122.txt

Posted: Wed Aug 30, 2006 6:08 pm
by sansoo
The 308 fields are an in-depth marketing survey. It ranges from radio listening habbits to where you buy your groceries from and just about everything inbetween.

They were all placed into a single table because my client wants easy access to this information. Why i dont know. And without building a backend application for the client to sort through all of this information i at first thought one large table to store all of this would be easiest.

Now as the project progresses im seriously doubting that decision. :oops:

Posted: Wed Aug 30, 2006 6:18 pm
by feyd
I guess you didn't understand what I was asking for. I want to see all 308 of these fields, their types and what each is for. From there we may be able to suggest places to break the table apart. Then there's understanding how the advertisers want to use existing data can determine how the tables reference each other to tie it all into a big unit.

For instance, if the advertisers cannot use an existing section in subsequent entries, then a one-to-one sounds right. If they can, then a one-to-many from the separated section to the parent (respectively) sounds more correct. However, if they expect to edit an existing section without affecting others then it's a one-to-one again, where you add automatic copying of the previous section to a new version that ties to the new entry.

Posted: Wed Aug 30, 2006 6:38 pm
by sansoo
I understand now. Here is a link to the original form i was given and told to use. I have changed it some but can not show any of my pages but this is exactly what everything ive done is based off of.

http://avant.freehostia.com/profile.html

This is what posts to a USER table. Each entry is linked to the USERS personal information. This is the table info. that needs to be able to be changed at any given time.

The CLIENT side needs to correspond to this information for each campaign they want to run.

This is where i thought of breaking the CLIENT side database into seperate tables roughly corresponding to the major headings of the USER form.
The CLIENTs need to be able to select as many or as few of the major categories as they please without having to scroll through the entire form.

Depending on which heading they choose a subsequent mini-form will be pulled up for them to fill out. This information will not change and expires after a given amount of time.

This is why i thought of doing a ONE to MANY approach.

I hope this is what you were asking me for.

Posted: Fri Sep 01, 2006 7:14 am
by GM
sansoo wrote: They were all placed into a single table because my client wants easy access to this information. Why i dont know.
This could be a good point to start reading up on database Views in the manual...

A view allows a single window onto data which hides the underlying complexities of multiple table joins.