How to Identify Relationships in SQL Data for HTML5and CSS3 Programming - dummies

How to Identify Relationships in SQL Data for HTML5and CSS3 Programming

By Andy Harris

After you normalize the SQL data, you’ve created the entities (tables). As an HTML5 and CSS4 programmer, you need to investigate the relationships among these entities. Three main types of data relationships exist (and of these, only two are common):

  • One-to-one relationship: Each element of table A is related to exactly one element of table B. This type of relationship isn’t common because if a one-to-one relationship exists between two tables, the information can be combined safely into one table.

  • One-to-many relationship: For each element of table A, there could be many possible elements in table B. The relationship between mission and hero is a one-to-many relationship, as each mission can have many heroes, but each hero has only one mission. (My heroes have attention issues and can’t multitask very well.) Note that hero and mission are not a one-to-many relationship, but a many-to-one. The order matters.

  • Many-to-many relationship: This type of relationship happens when an element of A may have many values from B, and B may also have many values of A. Usually, listed fields turn out to be many-to-many relationships. In the hero data, the relationship between hero and power is a many-to-many relationship because each hero can have many powers, and each power can belong to multiple heroes.

You can use an ER tool to diagram the various relationship types.

image0.jpg

Note that MySQL Workbench doesn’t actually allow you to draw many-to-many joins. You can emulate many-to-many relationships with a special trick called a link table.

ER diagrams use special symbols to represent different kinds of relationships. The line between tables indicates a join, or relationship, but the type of join is indicated by the markings on the ends of the lines. In general, the crow’s feet or filled-in circle indicate many, and the double lines indicate one.

ER diagrams get much more complex than the simple ones here, but the one and many symbols are enough to get you started.