Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
Could you tell us more (or give examples) about what you mean by Events, PageViews, EventsCustomProperties, PageViewCustomProperties and how they relate to your visitor? –  Emmad Kareem Jul 13 '12 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 '12 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 '12 at 1:28

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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