CODEWITHSUNDEEP
databasedatabase-design
Marcus
Marcus

Reputation: 5447

Basic Database design question

I'm designing the database for a WCF service I'm building and have a question about how I should design it:
I have a Subscriptions table, an Events table, and a lu_Type table.

**Subscription**                  **lu_EventType**
SubscriptionId int  ID PK         TypeId      int ID PK
CustomerId     int  FK            Description nvarchar
TypeId         int? FK  
Description    nvarchar

**Event**  
EventId        int  ID PK  
CustomerId     int  FK
SubscriptionId int  FK  
EventTime      datetime
TypeId         int?
Description    nvarchar

A Customer can have multiple Subscription's.
The TypeId is nullable because there are two types of Subscriptions that a Customer can be subscribed to. Known events, which will be from the lu_Type table, and unknown events, where the Subscription has a null TypeId and only a Description.
Once an Event is logged, it will be displayed in a website based on Customer.

  1. For known events, when the Event will have a TypeId, should I include the Description of the Event in the record? Or should I just leave the Description blank? It would take up more space in the db if I included it, but it would make retrieval/display much easier. I don't know enough about the inner workings to know if either of those is a non-issue or not. Or is there perhaps a better path altogether? (I can only have one Event table though, it needs to be generic.) Thoughts?

Upvotes: 0

Views: 329

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

  • Customer can subscribe to many events.
  • One event can be delivered to many customers.
  • Event can be known or unknown. A known event is an Event (sub-type).
  • Known events have all columns as unknown events and few more specific columns.

enter image description here

Upvotes: 1

Adam Robinson
Adam Robinson

Reputation: 185613

If the following are true:

  • The description of an event is directly related to the type
  • All events of the same type should have the same description
  • Changes to the event type's description should be reflected on existing events

You should not include Description on the event, but instead join to the event type table to obtain it.

Otherwise, the description should be included on the event record, since it is not directly related to the event type.

Upvotes: 1

Related Questions