A generic example that will help me understand database relations better:
3 tables:
| Credit Card |
| ID |
| Card # |
| Credit Card Type |
| ID |
| Type |
where CreditCardType.Type could be AMEX, Visa, MasterCard.
| Customer |
| (PK) ID |
1.) It seems that I don't need an ID field for CreditCardType because the Type is the Primary Key defacto. Is it beneficial to have a numeric field for a PK, even though there is a varchar that is unique?
2.) Because each credit card has to have one, and only, type... Do I setup a 1:1 identifying relationship between Credit Card and Credit Card Type?
3.) A customer can have 0, 1, or more credit cards. So I create a 1:n relationship between Credit Card and Customer. When I do that, MySQL Workbench adds the Customer.ID field and the Customer.PersonTypeID field as primary keys. Is that what I should be doing, or do I only need to add the Customer.ID field as a PK. Or should it be a FK?
| Credit Card |
| (PK) ID |
| Card # |
| (PK) Customer.ID |
| (PK) Customer.PersonType |
or
| Credit Card |
| (PK) ID |
| Card # |
| (FK) Customer.ID |