Search code examples
mysqldatabase-designentity-relationshiperd

In ERD modeling Does A Relation Map To A Database Table

The following is an Entity Relationship of a a Baseball League. enter image description here

I'm having a bit of confusion understanding Relations and Attributes of Relations.

An description of the diagram follows:

enter image description here

According to the description, Participates is a Relation and Performance is an Attribute (complex) of Participates.

Questions:

  1. How do Participates Map to actual tables in a database?

  2. Would there be a Participates table with the fields that define Performance?

    {Hitting(AtBat#, Inning#, HitType, Runs, RunsBattedIn, StolenBases)}, {Pitching(Inning#, Hits, Runs, EarnedRuns, StrikeOuts, Walks, Outs, Balks, WildPitches)}, {Defense(Inning{FieldingRecord(Position, PutOuts, Assists, Errors)})}

  3. Similarly are Plays_For, Away_Team and Home_Team also tables.

  4. As you create tables in a database (say MySql) how are Relations differentiated from Entities / Objects like Player, Team and Game.

Thanks for your help.

Solution

  • Question 1: Participates would be an actual table with foreign key columns for Player and Game as well as the column(s) for Performance. All M-N relationships need to be modelled in a separate table.

    Question 2: To keep it as a semi-decent relational DB you would have to separate all the info into separate columns so that each column would only hold one singular data. If you didn't separate the data you would break the first normal form and would probably run into problems later in the design.

    Question 3: As these three are 1-N you could also implement them with columns on the N-side. In the Game table for example you could have two foreign keys to Team table as well as all the data about the relationships in columns. For claritys sake you could make those relationships as separate tables also. As a sidenote: are you sure Player-Team is a 1-N-relationship so that a if a player changes teams the history-info about the StartDate and EndDate of the previous team is immediately lost?

    Question 4: They are all treated absolutely the same - no differentiation.