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.

• HLGEM: I'm thinking this may be a bad choice for a relational database, I'd suggest doing some research into how to use noSQL databases.
• JeffO: Do they just have the ability to enter data in isolated table(s) or will the tables need to be related?
• Jono Brogan: @HLGEM I have had a quick look at noSQL (wikipedia) and it says that noSQL databases may not give full ACID guarantees and that they are used when the performance and real-time nature is more important than consistency, if I am interpreting this correctly I dont think noSQL is an option because the consistency of the data submitted is important, if this is not the case could you explain further

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")

• NoChance: The op says that "part of app allows the users to create a form to allow customers to sign up to the users website", this indicates that each 'user' has 0,1 or more 'customers'. You need to take care of that in your design.

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.

• Jono Brogan: Hi, I have asked a question to HLGEM on my post above and if what I have looked up is incorrect, would it make sense to use a relational database with my web app as although I have not expanded on what the app does there are clear relationships between the data and then use a NoSQL database to handle the data submitted to the users sites deployed through my app?