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?
Best Answer
Typically you would design the database such that someone coming into the database alone could get a handle on how the application worked. Had they seen such a foreign key they may not be aware of it's implications.
You could approach this in two ways: First, you could do as you mentioned. Create two separate tables and handle them there.
Second, and the way I would recommend, you would create a table to handle all the common fields between the two, and then two other tables, one for internal-specific data, and one for external-specific data. Each of those tables would have a primary-foreign key to the common table. I.e. a Guid or ID.
The downfall of the second method is the application would be required to be aware of the two different tables, and depending on the method you are using to implement it (.NET, PHP, Python, etc) you may find one method easier than the other.
I personally use .NET and C# or VB, depending on the project, and in this situation I would go with the three-table design, as I would create an abstract class ("Event") and then two classes to inherit it and all the common properties ("ExternalEvent", "InternalEvent"). The Event class would handle the interaction of the shared properties, and the InternalEvent and ExternalEvent would handle the logic specific to them.
The upside to this approach is the polymorphism of it. If you add a common field to the two in the future (for whatever unprecedented reason) you only have to add/change it in one location.