Table structure for events

database-design

Got a bit stuck, need a hand.

I need to store events at the database, and events may happen at

different location
multiple locations
at the same time

so these are my tables

CREATE TABLE location(id, name, address)

and

CREATE TABLE event (id, name, date)

Where should I add the location in this design?

The simple (id, name, date, location_id REFERENCES location) won't do, because one event may happen at different locations and I don't want to store 2+ rows of the same event since event is unique.

Best Answer

Hmm... I'm a bit confused. By definition, if two events happen at different places, then they're different events.

My advice in this scenario is to tave 3 tables as follows:

CREATE TABLE Event
(
  Event_ID INTEGER, 
  Event_Description INTEGER,  -- FK   
  Event_Location INTEGER,  -- FK
  Event_Date DATETIME
);

And then two other "parent" tables

CREATE TABLE Event_Description
(
  Description_ID INTEGER PRIMARY KEY, -- FK in Event table. 
  ED_DESCRIPTION VARCHAR(50));
);


CREATE TABLE Event_Location 
(
  Location_ID INTEGER PRIMARY KEY,  -- FK in Event table
  Location_Description VARCHAR(50)
);

Related Solutions

One address table for many types of addresses

Unless the attributes between the different types are totally different (which I doubt), go for the second option and store the address type in a column of the address table.

Sql-server – table design to track significant events associated with an item

Normalize always unless you have a very good reason not to. It should not be flipping a coin.

Neither of your proposed designs seems optimal to me for a variable / unknown number of events, since when "that number may increase or decrease," in both cases, you have to change the schema and the code.

I also don't believe that you have to base your core table design around the format of the input files. This is why we have staging tables, ETL processes, SSIS, C#, etc. There are many ways to transform data from flat files into a format that is more appropriate for a relational database. Don't design your database because of what your flat files look like.

So you could have staging tables that look like the input files, but then you would load the data into real tables that look perhaps like this:

CREATE TABLE dbo.Events -- lookup table describing events
(
  EventID INT PRIMARY KEY,
  Description VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.OrderEventLog -- actual event data
(
  EventID INT FOREIGN KEY REFERENCES dbo.Events(EventID),
  OrderID INT FOREIGN KEY REFERENCES dbo.Orders(OrderID), -- guess
  ExpectedDate DATETIME2(7),
  ActualDate DATETIME2(7)
);

Now when you have a new type of event, you just insert it into the dbo.Events table, and then you can insert related events into dbo.OrderEventLog without having to change the schema of either table, and without having to change the interface to your stored procedures or bulk inserts into the staging tables either. This also keeps the history table (and all of its indexes) nice and narrow, and allows you to build indexes that favor searches for specific types of events.

You can always flatten the data later - which is typically what you do when you get to a point where you are building a data warehouse around the data.

Best Answer

Related Solutions

One address table for many types of addresses

Sql-server – table design to track significant events associated with an item

Related Question