I could be barking up the wrong tree, but I'm trying to illustrate not only what the relationship is between entities, example, Customers shop in many stores, but also how to join the two entities. For example, two tables:
Customer Data
CustomerID | Name
11112 | Monkeyhead
11113 | Monkeybutt
Customer to Store data
CustomerID | StoreID | Lifetime Purchase
11112 | ABC | $23
11112 | DEF | $45
I know that it's a many to one relationship between the two entities, Customer Entity and Customer to Store Entity; but how do I illustrate that it's joined via the CustomerID columns?
Is crows foot notation the right one to use? If not, which is?
Best Answer
There ought to be another entity, whose key is StoreID. There is a many to many relationship between stores and customers.
In a simple ERD, you can just draw a line between the two boxes for Store and Customer and put a crow's foot at either end. You can't put lifetime purchases in either of those two boxes. So if you want to include that attribute in your diagram, add a diamond in the middle of the line you just drew.
The diamond diagrams the relationship, and you can put lifetime purchases in there.
When you go to design a relational database, all of the attributes will end up being stored in relational tables.