SQL Server – Removing Chat History for a Particular User from Database

Relational DBMS is not ideally suited to this type of application. RDBMS is great for tracking and enforcing referential integrity for relations between two entities, THING A and THING B. What you want to do is to track relationships between THING A and every other thing in your database (or lots of other things, at least).

Your options, each of which include some form of compromise, include:

• Sparse Foreign Key Columns: Create an activity table that has mutually exclusive foreign key columns that point to the various kinds of activity that you want to track. This is a fully 3NF way of doing things, but it still requires new columns to be added for new kinds of activities and it results in sparse columns, which people try to avoid as a rule.

• Forrest of Tables: Create individual activity tables each of which track a particular type of activity. You can then create a query or view that uses UNION ALL to bring the various kinds of activity together into one place for querying. This is also very by the book with respect to normal forms, but you end up with a database cluttered with activity tables and you have to create a new table and alter a view whenever you add a new type of activity.

• Data Driven Joins: Use a single activity table that captures the type of the activity and the instance of the activity in one place. You would need to have more sophisticated logic built for retrieval of the activity details, since this approach foregoes formal foreign keys. There is no declarative referential integrity and the approach smells a lot like EAV, which makes many people's skin crawl. On the other hand, you wind up with a single table, no sparse columns, and the addition of a new activity type requires no schema changes, only a tweak to one piece of code - the one that reads type of activity and knows what to do to go and retrieve the details of that activity.

You need to look at where your sensitivities are going to lie. How many types of activity will you ultimately be tracking? How often are new types of activity going to be added to the mix? Are you prepared to endure the scorn of colleagues when they see you've implemented a solution with sparse columns (or worse) EAV? When you answer these questions you'll be able to pick the approach which makes the most palatable compromises for your application.

A a rule of thumb: don't pre-optimize for performance. I think a lot of developers assume that joins are inefficient and they don't trust DBMS to do what it's built to do.

Start with a properly normalized design. Make sure your indexes and queries are optimized for your particular balance of reads and writes.

If and when you start to find that performance can't keep up with the best hardware you can afford, then start to think about denormalizing.

If you denormalize early you are just setting yourself up for maintenance headaches down the road.

Looking at your suggested table layouts, I would suggest that you're trying to make TOPIC do too much. Anything which could appear in POST (e.g. poster_id) almost certainly doesn't belong in TOPIC. I suggest you adjust your thinking a little bit. I get the impression you are thinking very much about what topics and posts are going to look like on the page. This may be leading you to thinking of topics as a small super-set of posts, whereas they are probably rather more like subject headings. The fact that you plan on displaying the first post under each subject heading along with the heading isn't a good reason to co-mingle posts and headings.

I think you may want to rethink some of your cumulative total columns too. Thinks like up and down votes may need to be tracked in their own tables. You may need to do this to keep people from up or down voting repeatedly and to allow people to rescind their votes. Similarly, you may want to know all editors, not just the last editor.