mercredi 25 février 2015

Using the same table for entities that are almost alike when it breaks foreign key integrity?


I'm designing a website with a database that should support events that a user can sign up for, fx. a concert. Users can sign up using our internal registration system, or using an external website (which we just redirect to).


So events can be internal or external. Both types of events have the same type of metadata such as date, title and place etc. If the event is internal then some can tables refer to the event ID using a foreign key, fx. the many-to-many table users_events, there's also some tables that handle billing-data related to internal events.


My current setup is a single event table that has the columns I mentioned before, date, title, place etc. It also has an external_signup_url column, which is NULL when an event is internal, and contains an URL for when an event is external. The problem with this design is that the tables that references an event ID by a foreign key can potentially reference an external event, which makes no sense at the application level.


So my question is, even though the two types of events are so similar (attribute-wise), does the small difference, and the potential non-sensical foreign key reference, really warrant separate tables, fx. external_event and internal_event?





Aucun commentaire:

Enregistrer un commentaire