Web application - database design/infrastructure

Question

I have just begun development of a web application (that will be deployed using Google app engine), without going into the specifics, the web app will allow users to deploy a small website of their own, part of app allows the users to create a form to allow customers to sign up to the users website, however each user may require to collect different data on its customers, for example one user may require a customer’s first name and email address only where as another may also require the customers age.

My question is what would be the best way to structure the database(s)?

By structure the database(s) I mean how can I implement them so that I can store the different data collection sets for each user and then store the information that is retrieved by each of these users from their customers? For example I don’t think this is right but my initial though was that I would have to deploy a database for each of the users but this seems like overkill, or add a new table for each of the users (I don’t even know if these two are options). I have only had experience with trivial databases and this problem has me confused.

Answer 1

I would suggest that you look into Cassandra or MongoDB. They both are, what is commonly known as NoSQL databases.

Some advantages that NoSQL databases have over traditional relational ones are:

• Helps in Scaling
• Flexible data model
• Distributed and Fault tolerant

Answer 2

That part of your application is not very "relational". You would need to implement a key-value schema such as:

users
  user_id         pk

attributes
  attribute_id    pk

user_attributes
  user_id         references users(user_id)
  attribute_id    references attributes(attribute_id)

or something like PostgresSQL's hstore. This would let keep you all of your data within a relational database if it suits the rest of your application; you will have to judge how "relationalish" is your entire application.

Answer 3

The data pattern you describe is not relational. You could use a relational database for this, but it is not the right tool. As suggested you should use a non relational database, such as mongodb(document database), couchdb, or something else. If you insist in use a relational database, you could create a table where the user attributes is stored as rows instead of columns, but keep in mind that this is not the best option and could be problematic in terms of performance when the table becomes large. The described schema could something like this:

// specific user Information Create table users_info (userid, info_name, info_value)

// common user information Create table users(userid, username, site_name)

//sample data insert into users(userid, username, site_name) Values(1, "nohros", "nohros.com")

insert into users_info(userid, info_name, info_value) Values(1, "ssid", "6664312")

insert into users_info(userid,info_name,info_value) values (1, "ssn", "123456789")

Answer 4

There are two ways to go here.

Use a NoSql type database and have some pretty complex logic in your code to pull everything together.

Or

Generate a relational database schema, plus all the classes/code to handle it for each website. You have a fairly complex code generator, but, simple straightforward and easy to debug code for the actual website.