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.