I am working on a Chen ERD to model our organizations merchandise. Our central entity is a Style. We have supplemental entities of Color and Season.
I am defining our assortment as the relationship between these three entities, and this relationship itself will have attributes and is defined by the three entities which participate in the mandatory relationship. The rules are;
- Many Styles can be offered in a Season, and a Style can be offered in many Seasons.
- Within a Season, a Style can be offered in Many Colors.
I then have 2 other entities, one of which I believe is a weak entity, Climate, and the other may be weak, but I am not sure, this being Transaction Channel.
I am thinking of these as relationships off of a relationship? Meaning, for a given Style/Color combination offered in a Season, it can be available through 1 or more Transaction Channels. Additionally, within a season, a given Style/Color combination can be intended for 1 or more Climates.
Is it valid to have relationships off of relationships? Or does this requirement dictate that I should think of this Style/Color/Season relationship as an entity itself, and define the relationships to Climate and Transaction Channel off of this entity?
0
A relationship is a way to link rows in tables. The start and end points are tables (or views). It doesn’t make sense to talk about relationships off a relationship per se, however, you can have a link table (for instance used to express a many-to-many relationship like you have with style and season) which you can create relationships to/from.
In your case the data model could look like this:
- style: a style that can be offered in various colors in various seasons.
- color: a color that the style is offered in.
- season: a period of time in which the style/color is offered.
- channel: a party making the style/color available for a given season.
- climate: a climate for which the style/color/season is intended.
Since a style can be offered in many seasons and a season can have many styles, you have a many-to-many relationship that you need to break up with a link table. Now, you also say that within a season a style is offered in many colors. This leads us to the following combination: season-style-color. In plain English, a season has many styles of which can have many colors. I’m assuming that the colors may vary for each season. I believe this triplet represents a concrete product (e.g. “a red sweater for winter”). This leads us to:
color
^
|
style <- product -> season
In other words, a product is what’s being made available to the channels you mentioned. Furthermore, a product is suitable for a particular climate.
In terms of tables:
- style: style_id (pk), …
- color: color_id (pk), …
- season: season_id (pk), …
- climate: climate_id (pk), …
- product: (style_id(fk),color_id(fk),season_id{fk)) (pk), … (see note below).
- channel: channel_id (pk), …
- availability: (product_id(fk),product_id(fk)) (pk), …
The notation “(col_1 (fk), col_2, …, col_n) (pk)” is my attempt to express a composite primary key. A composite primary key is made up of more than one column and the columns can be foreign keys or regular business values. This allows you to express a product as a combination of style, color and season. Furthermore, you can express when a product is available for a particular channel (the “when” part is part of the season which is part of the product).
Does that make sense?