Designing and Optimizing Associative Database Models for Scalable Social Web Applications

Usually I design web application databases for MySQL, but the methods listed here are compatible with Oracle or any relational database management system. I’m not going to guide you through your own design process, but here are the basic database design principles you will need to know as you optimize your database for social applications:

– Use unsigned numeric integers (properly scaled for the size of your tables) without leading zeros.

– Start out with third normal form (3NF). Use associative tables to eliminate redundancy at first.

– Avoid storing files in the database. Use numeric filenames to associate data records with the file system.

– If necessary, use table caching and views to optimize performance based on use patterns. This should be done in conjunction with software app testing.

– If you need to shorten URIs passed between APIs or URLs for the user, make a relationship table referencing the longer URI with a shorter handle. Use numeric handles for small data sets or alpha numeric for larger data sets.

– For time related queries, associate the numeric primary key of long tables with relevant timestamps (hourly, daily, weekly, monthly orĀ  other time intervals that your social web application would query.) This will reduce query time for long tables.

Tagged with: , , , , , , , , ,
Posted in database architecture and data modeling