CODEWITHSUNDEEP
sqldatabase-designrelational-databasedata-modeling
Dolda2000
Dolda2000

Reputation: 25855

Storing records of slightly dissimilar types in a RDBMS

I know SQL, but I'm not terribly experienced with it. I have a system in which I would like to log user logins, logouts and other security-related events in an indexed database to be able to pose manual queries to it, and I figure some SQL-based RDBMS should be the best fit for the job.

However, the records I'd like to store have similar, but not identical, data. All records would store a timestamp and a username, but the other data items would differ. For instance:

How should one model something like this in a relational database? I can imagine at least three possibilities:

Clearly, each one has its own advantages and disadvantages. I do realize that this is a somewhat subjective question and is also likely to depend on actual use-cases, but I imagine there ought to be standard/best practices for this kind of thing that I just haven't seen. Are any of my suggestions reasonable or standard? Is there some other option that I have missed that is better?

Upvotes: 1

Views: 82

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

The solutions you mention appear in Martin Fowler's book Patterns of Enterprise Application Architecture. You might like to read that book to see what he says about using these patterns.

Use different tables for each kind of data item

Add columns for all the different data items and leave them as NULL for records that don't use them

Use one central table with the common data items, and auxiliary tables that store the rest of the data, linking to an event ID in the central table

Fowler also covers a fourth solution for this problem:

Upvotes: 1

Related Questions