0

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.

Improve this question
3
  • Could you tell us more (or give examples) about what you mean by Events, PageViews, EventsCustomProperties, PageViewCustomProperties and how they relate to your visitor?
    – NoChance
    Jul 13, 2012 at 20:06
  • Sure so if you think of an analytics style scenario, it's very simple and event is like click, tap, load... page view is what is says on the tin. custom properties may be for example event login may have a custom property gender, page view may have custom property id of the article the page is loading.
    – Daniel
    Jul 13, 2012 at 20:28
  • Did you look at Google-analytic? before going forward and spending time, i would suggest to look at available options.
    – Yusubov
    Jul 14, 2012 at 1:28

1 Answer 1

Reset to default
2

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.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.