Few questions, though i'll ask it all in 1 post.
1) Any difference between a Lookup & reference table? And what is best practice on how many to use? Example List of Cities, Countries, Account types, Category names, User tags, Movie names - are these all lookup, reference or some other type of tables? Key is all this data will be search able and part of user analytic hence I am trying to centralize items like movie names, company names, etc instead of storing as plain text.
2) Also any suggestions if the differen table types (user tables, system tables, lookup tables, etc) should be named differently like add a type Prefix before the name to distinguish between them or use same name? Using MySQL, currently we are documenting table relationships in excel.
3) Any "free" tool to use where we can pull all table relationships, keys per table, lookuo values, etc for documentation purposes or do we have to manually query the database and get all this?
4) How much to normalize tables? Since this a use content website I am working on, lots of user data will flow in and scalability/performance is key. My site is similar to yelp but with much more social functionality and detailed privacy and user networks.
5) Since the site is like yelp, there are many cities and targeting many countries in many languages. Best way for users to switch between languages? - Session based, URL parameters, seperate subdomain, subdirectory? Key requirement is there will be multi language on same page, for example a review on hotels. 10 people can review in english, 2 in french. So as a user if i am on the english flow then it will show 10 english only and have a dropdown to even display the 2 french without having to change the entire page to french. Same, if on french flow -> display the 2 french and option to display 10 english also. There are 20 supported languages for launch.
6) User tracking -> Basic architecture is -> User takes an Action. Action is on a page, about an object, has a pass/fail status and may involve relationships (other people). This together is called an activity footprint. When this happens, system launches an Event handler to record the data and pass on for analytics. At the same time it fires off a notification handler to check if the activity is to be sent to user as a notification based on the user defined notifications. (like your facebook notifications). Is this a good design to capture all user activities and send off notifications keeping performance, scalability in mind?
7) User analytics -> Based on tracked data, I am showing users all the data back based on pre-defined and user selected custom metric. Use same database or have a separate data warehouse for this?
8) How much business logic should be done at DB level and how much as code level? Ofcourse since this a startup and costs are key, I dont want to over strain the database and need to keep infrastructure stable which means reduce database load as much as possible, but for scability it makes more sense to keep as much in database then in code so changes can be made globally easily and quick. Feedback?