Data mapping across websites?

I am starting work on a project which has multiple websites for a client. For 'analytic' purposes need to measure metrics across websites over a period of time. This means i need to centralize the data model and all the possible questions/answers/lookup values so it can be used across websites. The question i hav开发者_如何学Pythone is:

Example: Age range of a user visiting website 1 is say: 30-39 years old. (We ask for age range when they enter). so in the data model I have a lookup table for answers which has all possible answers used across all websites. So (30-39) has PK ID of say 102. Now in website 2, same thing, so again (30-39) has PK of 102. This way i can measure across websites the same age range. But the problem is where or how to store the user's answer and map that to this ID?

If i have a table called say UserAnsers, it has an AgeRange colunm. Do i make this a FK to the Answer table at PK 102 to store (30-39) for the user? if yes, then what value gets written in the Useranswer table, would it be 102?

Secondly I need to measure textfields also. Like how many are complete across websites. So say "email address" field. I give this textfield a field Id of 10. Again when i write the consumer' email of say xyz@abc.com in the 'email' colunm of the the answers table how will i link this to the field ID 10?

---

I'd probably make an API for each website that dumps the data I want in a consistent format, which I then could load into a separate database, where I would then do the metrics on.

This gets unfeasible if the data is so large it takes hours every night to migrate the data. But otherwise it's a solution that has the benefit that you don't have to touch the current schemas.

If the sites doesn't exist already, then I'd just force a consistent datamodel on all sites, which is trivial. It doens't matter where the mapping between "30-39" and 102 is mapped, the only thing that matters is that it's the same everywhere, which you then set up when you create the databases.

If you want the values and schemas to change a lot, then using just one database for all sites would probably be better, but if you can't do this, then make an export for each site.