Hi!
I've been reading these forums for a while and I really like them lot's of good info.

but I have a problem that I could'nt find a solution to yet.. so i'll ask :P

the DB is going to store a log(events). (not hard)
the problem is that each log-entry has different layout.
each log-entry have some fields in common (3)
then 3 fields that most of the events have
then additional 0-3 extra fields that differ from event to event.
(som events have additional 20-30 fields but thats only 2-3 events so they get their own tables.
well... the extra fields has to be searchable so i can't just use one field and put everything in a string, an every field needs a caption.
there are about 100-200 events... anyone have any good idea how to solve this? I've been thinking for a week and can't find a solution I like.

thx in advance...!

To have info that stores and is different from entry to entry try this

[Event] ---< [joinTable] >----- [Field Type]

Event is obviouse so I'll leave that

Join table would have three fields EventID, FieldID & data. data should text with max size equall to the biggest required.

The two ID's make one key and the [field type] table should be the field lable and probably an autonumber. The effect is that any given event can store a single datum or many data under different "headings" or field names. It can be thought of as a dynamic table... it's not but it's an easy way to think about it.

Does that help?

not sure... my english is'nt what I wish it were
but I think I've understood the idea...but I'm not sure about everything.
I'm currently testing different solutions and maybe I'll post some of them here later.

right now I have 2 solution-ideas, one with union and one with join
since i want to be abe to make a result table that is easy to read which would look someting like this:

event_id | value1 | value2 | value3 | spec-value1 | spec-value2

the union idea uses 3 tables,
one with event_id - value3
and one with event_id - spec-value1
and one with all fields

is that a bad way to solve the problem?

the other way I'm thinking about is having one main table with event_id - value3 + one additional field id (auto incr)
and then one more table with: id | field | value

one type of event always have the same headings but it differs from event to event. so I will probably have one table that describes each event too, hmm... this is making me go crazy...from where will I get my headings?

I'll give it al little more thinking...

It sounds like you might want to look at the thread in Better Relational design I've attached a screen shot from a recent database I have been working on.

You will notice the Qestion and Answer relationship.

The way I have used this is to read the question for the answer via the direct relationship. BUT to determine the relivant questions I have used a query to look up via the long way around.

In this kind of operation it is the "Logic level" or method used that interprits meaning from the DB. On the diagram one address must answer questions baised on which categories the owner (entity) is in. Each category carries many questions and these questions crop up in many categories.

I use another query that uses the GROUP BY option to remove repitition from the question list.

hope this helps.

Another way to look at this problem is one of decomposition. You could create a table that had only the primary key and the attributes (columns) for all events. Create another dependant table (with a foreign key relationship within its primary key) that contains the columns that belong to most events. Repeat this process as necessary.

If you are unsure about possible future attributes, you can include an EAV (Entity-Attribute-Value) table at the end of the list that will allow you to add attributes onesie-twosie to individual rows if needed. This is a really slippery slope, so don't do it without considerable thought.

Din Engelsk är bättre än min svensk!
-PatP

You want to be able to write the log entry quickly- without slowing down the application. You want to avoid executing a bunch of lookups, updating indexes, etc.

The features necessary for quickly handling logging are the exact opposite of the features needed for efficient searching.

So I would ask:

How many events (average) per hour? Are there any "peak" times when a large number of events occur? How many/how often for the peak times?

Do you need to query on events that happened 2 seconds ago? two hours ago? two days ago? two years ago?

Thx for all help... I'm getting closer

yes, insert is more critical than search
searching may take a while, but not too long, a few sec.s

A1: I've been trying to do a little data analysis but I've only been given logs from an hour, but I'm pretty sure the peak:s won't be much more than the avarage insertion.

A2: hm... about half an hour ago I think but minutes whould be much better

good questions btw! i should have thought of them and included them in my first post.

EDIT: A1 continued: I can't check how many insertions it isin the log-samlpe i've got right now, I'll add it as soon as I can!

NOTE: I am assuming that one of the three standard fields is an ID field that will uniquely identify a log entry.

Here are you basic options:

1. Create 200 tables- one for each type of log entry

2. Create one table with the three standard fields, one generic text field into which you can add any number of additional values (e.g. comma separated, name/value pairs, XML), and one Entry Type field that will tell the application how to interpret the generic text fields.

3. Create one table with the three standard fields, three additional generic fields for the values that differ (e.g. F_1, F_2, F_3), and one Entry Type field that will tell the application how to interpret the three generic fields.

4. Create one table with the three standard fields and an Entry Type field to identify the type of log entry. Then add another table to store all of the fields that change: it needs a foreign key field to reference the ID of the log entry, and then a FIELD NAME and a FIELD VALUE field to store the info.



I can't tell you which one is "the best" -- just try to balance the following:

Complexity
Think about the application that will need to support each solution… How much knowledge does the application need to have?
What about the developer that will need to fix/extend this solution… Will they understand the DB solution without having to dig through the code?

Maintainability / Extensibility
If the log entry types change- will you need to rewrite the application? Redesign the data model? Both? What if you want to have four extra fields instead of three?

Integrity
Data Types? Relationships?

Performance
How fast can you add an entry to the log database tables? How quickly can you assemble and retrieve all of the values for an entry?

the way i see it is that we should at least refine the requirements -we're all trying to solve the problem at the technical level...

hhhhmmm... should we try seeing if we could simplify the problem on the business level, then maybe things would look a little cleaner on the technical level... i mean "three fields for around 200 events," "then a couple of events having a larger number of fields," or how about "each field should need a caption"?

cheers!!!