sbaer (5/6/2014)
I am fairly new to database design and haven't been able to find a lot on this question.I work for a marketing and advertising firm. We don't have a "transaction" database since all of our data comes from our clients. I'm responsible to load it all into a database so we can remove duplicates, clean the data, and run analytics.
We normally create a separate database for each client for security reasons (decision made by IT security). But most of these databases has some things in common, mostly address data.
Have a talk with the security guys and find out exactly what the requirements are, what you must comply to etc. A mutual understanding on this is imperative!
I want a central repository of things like zip code data (zip codes linked to county, regions, etc.), country codes, state codes, etc. Usually, this repository would be used just for the initial insert and cleanup. I'd have to enter it into the client's database the way the client wants to see it.
If possible, establish a single data collection point. From there it is easy to distribute the client's data to the appropriate destinations. In the central repository, maintain all the the referential data that is NOT client specific.
Any advice, thoughts, or references on how this is best implemented? Are there problems associated with linking data like this between databases? Is there an easier way to keep a single zip code table but have it be accessible to several address tables?
All reporting repositories should be client specific!
Hopes this helps, feel free to inquire further.
😎