I have an embedded database (SQLite), it stores information about events and page views, its purpose is to track the user journey inside my application. I need to provide support for optional custom properties on my events and page views.
In code this is not a problem, however I am torn between two solutions for storing that information.
So I have Events and PageViews tables, my first option for persisting the optional custom properties is to have a EventsCustomProperties table which has only a name and value column and a reference (foreign key) to the event.
In this solution I would also have a PageViewCustomProperties with the same structure.
The problem I have with this is having two tables which are identical other then their relationships with other tables.
In my other solution I would have one CustomProperties table, and in addition to the name, value and relationship key, it would also have a type column, which I should use manually in code to map the relationship id to an event or a page view.
In this solution the actual use of foreign keys is redundant.
My dilemma is that in the first solution I am respecting one normalisation rule of repeating groups of data, buy in the second solution I am breaking another rule although I cannot get my head to remember the name of the rule.
So it seems I will break rules whatever I do. Can anyone suggest a better way or a preference to the first or second solution.
3
Here is one way of doing it. It is close to your first solution. This is a variation on a regular star-schema. Star-Schemas sometimes trade normalization for efficiency. The proposed solution below is based on my understanding of the problem, but this is incomplete, for example, to verify the correctness of this, you need to list the most common queries you’d expect to issue and see if this is valid or not. I have only listed the PKs.