CODEWITHSUNDEEP
databasedatabase-design
Fran Navarro
Fran Navarro

Reputation: 41

Relationship between tables

I have an entity called events can be either one or several types. My question is how do the relationship because a event can be more than one type. These can't be an attribute nor an inheritance relationship. Could be a weak-strong entity types as events not exist without the body but I have not clear.

example: Event is a workshop and a conference.

Thanks for yours help.

Upvotes: 0

Views: 59

Answers (4)

Fran Navarro
Fran Navarro

Reputation: 41

This pattern was usefull: multivalued attributes.
http://www.tomjewett.com/dbdesign/dbdesign.php?page=hobbies.php

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

If the list of all possible Types that can be associated with an Entity are defined in a list (e.g. within a single table), then you can use the standard many-to-many relationship pattern:

TABLE EVENTS
EventId, primary key

TABLE TYPES
TypeId, primary key

TABLE EVENTTYPES
EventId, foreign key to Events
TypeId, foreign key to Types
...with the primray key on {EventId, Typeid}

Upvotes: 1

Leo
Leo

Reputation: 6580

If you can't use a flag (attribute) neither use inheritance, then you'd have to artificially keep two entities, one for workshop and another for conference, keep some FK for each one and possibly a flag/trigger to ensure you're not using an FK for both at the same time.

OR

You could use some intermediary entity, but I understand this would be some sort of inheritance, because this proxy entity would act like a "super" entity for both workshop and conference (like an "event"...)

The first option is bad in terms of maintenance, I'd not recommend you that.

The second option is IMO more "intuitive".

If both entities are too close one another and will be linked to several other entities in the same way, I think you could just use a flag to differentiate them, with the risk that in the future, if anything changes to one of them, you have to refactor your schema, which is usually painful and risky. It's a premature optimization, and as we know, premature optimization is the root of all evil. So keeping them in separate entities may be a good option.

Upvotes: 1

Supercoco
Supercoco

Reputation: 61

A rule of thumb is that you should always keep in mind this when designing a database: each entity with its own table. This is the base of a good Relational Database Design.

Upvotes: 1

Related Questions