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 aSubscriptions table, an Events table, and a lu_Type table.
**Subscription** **lu_EventType**
SubscriptionId int ID PK TypeId int ID PK
CustomerId int FK Description 开发者_JAVA百科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.
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.
- For known events, when the
Eventwill have a TypeId, should I include theDescriptionof theEventin the record? Or should I just leave theDescriptionblank? 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 oneEventtable though, it needs to be generic.) Thoughts?
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.
- 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.

加载中,请稍侯......
精彩评论