Search code examples
database-designentity-relationship

What is the right form of this relational db design?

Below I have two tables, Customer and Currency.

Scenario : Each customer must have one currency defined for all their transactions. I have placed a currency_id column which associate the currency to the customer. Below are some question arise in my mind. Mybe it is silly question. But I would like to clear myself.

  1. View from Customer : One customer has one currency enter image description here

  2. View from Currency : One currency can have 0 or more customer. enter image description here

Here the question. In this types of scenario, how we should take the issue? We should see from customer (1) or from currency (2). Anybody can explain me this??

EDIT : My question is in terms of designing the ERD.

Solution

  • (Tossing this in after upvoting @Gilbert's reply and reading the ensuing discussion.)

    A relationship between two tables goes both ways: table X is related to table Y, and table Y is related to table X. For your case, it’s excactly as you said:

    • One customer has one currency
    • One currency can have 0 or more customers

    Both verbal descriptions are correct.

    With regards to ERD representation, your second case best represents this. Unless you’re an academic, don’t get bogged down with representational minutiae, there are dozens of very similar notations out there. So long as it shows “One to zero or more”, you’re good.

    As for implementation, I think you’ve already got that down pat: Parent table has Primary key (CurrencyId), child table has its own Primary key (CustomerID) and a foreign key (CurrenctyID) related back to the parent table.