Normalizing large amount of data for database

Question

I have a large amount of data I need to store in a database. The data is: for every day of the month, there are 5 events. The 5 events are further split into 2 different sub-events which need to be kept separate, meaning for every day of the month, there are 10 events.

At the top level, the 5 events have different headings and lower down, the odd numbered sub-events have one heading and the even numbered sub-events have one heading.

I'd like to normalize this data before storing but I'm struggling to come to a final db structure and am looking for hints on this. I have very little experience with normalization (this is for a personal project I'm working on) but I'd rather do it properly then dump everything into the db in one go.

Edit: Example of data as requested:

20th March:

Event 1: Sub-event 1: 4:30am, Sub-event 2: 5:00am
Event 2: Sub-event 1: 12:30pm, Sub-event 2: 1:00pm
Event 3: Sub-event 1: 4:15pm, Sub-event 2: 4:45pm
Event 4: Sub-event 1: 6:15pm, Sub-event 2: 6:45pm
Event 5: Sub-event 1: 8:00pm, Sub-event 2: 8:45pm

All the events repeat at varying times throughout the month and entire year.

Not ignoring any of the answers, I've been busy and haven't had a chance to take a proper look at them. — Al., Mar 27 '11 at 22:15

score 1 · Answer 1 · answered Mar 20 '11 at 16:29

1

Just have an event table with a row for each event. Subevent can have a foreign key to the same event to reference the event of which they are subevents.

answered Mar 20 '11 at 16:29

Jens Schauder

77,657
34
181
348

swisscheese · Answer 2 · 2011-03-28T20:50:21.637

1

Here is a simple model to achieve what you are looking for. Depending on how you will be using this data you can add Alternate Keys or modify the Primary Keys as you see fit. Let me know if you have any questions.

enter image description here

EDIT in response to comment:

Not exactly sure if I understand what you are asking for. Here would be the details though. The relationships between the tables are defined in the model. If you don't understand (forgive me if you do) the model I would recommend looking up ER Diagrams utilizing Crow's Foot Notation.

EventId (INT) : Incremental 
EventTypeCode (CHAR (2)) : Used to cover Events 1-5 as E1 - E5
Description (VARCHAR) : Info describing E1-E5
SubEventId (boolean) : Since you only declare two options 1 & 2

Hope this helps.

edited Mar 28 '11 at 20:50

answered Mar 21 '11 at 21:22

swisscheese

1,765
4
23
25

Could you give an example of the type of info. that would be held in each table and the relationships between the tables? – Al. Mar 28 '11 at 20:29
@Al see my edits in my answer. Let me know if I just didn't understand your question posted in the comment above. – swisscheese Mar 28 '11 at 20:51

Chris Walton · Answer 3 · 2011-03-28T21:05:15.067

These tables normalise the data:

   Event
      Date 
      Time
      Event Type (FK to Event Type)
      PK Date,Time,Event Type
      *Example Data: 2011-03-30; 04:30; 1 top;*

    Event Type 
      Type 
      Heading
      PK Type (Values 1(top)-5(top),1(odd)-5(even))
      *Example Data: 1 top; This is event type 1 top level;*

    Parent Event
      Child Event (FK to Event)
      Parent Event (FK to Event) 
      PK Child Event, Parent Event (and possibly Index each field independently)
      *Example Data: 2011-03-30, 04:30, 1top; 2011-03-31, 05:00, 1 even;*

EDIT: As requested example data added. The relationships are given by the foreign keys (FK), so the example is relating one top level event to one sub-event of typ 1 even.

Could you give an example of the type of info. that would be held in each table and the relationships between the tables? — Al., Mar 28 '11 at 20:28

Normalizing large amount of data for database

3 Answers3