September 14, 2017 at 10:47 am
Here's the business rules to make a new diagram:
1. One customer can only create one account identified by the customer’s email address. A customer can register only one email address. Note that passwords are in general not unique.
2. One customer account may store multiple credit cards and all those cards must have the same billing contact name and address. That is, the combination of billing contact name,address and credit card number can uniquely identify a credit card.
3. Multiple customers may share the same phone number and billing address.
4. Multiple customers may share the same credit card number. For example, all membersof a family may have the same credit card number. But, the combination of credit cardnumber and billing contact name can uniquely identify a customer.
5. A credit card of a customer is associated with a unique payment code.
How to draw ER model diagram using crow's foot notation?
September 14, 2017 at 12:18 pm
I think we would need more information to figure this out, consider the following:
1. "One customer can only create one account identified by the customer’s email address"
- A customer's e-mail address can change over time, do you need to track previous e-mail addresses? This would require e-mail to be a separate table then.
- If not then e-mail address can just be a column in customer table
2. "One customer account may store multiple credit cards"
- Seems straightforward offhand, but consider the same credit card may require multiple entries considering they expire or get re-issued
- I hope you have a plan for encryption of all the personally identifiable information here!!!
- "all those cards must have the same billing contact name and address" This sounds like it's related to customer, not necessarily credit card
- Each credit card should have separate billing names, even if you're requiring the address to be the same. Some of my cards include my middle initial, some chop off the r in my first name Christopher, and other strange scenarios occur.
- Do you need to track previous billing addresses? This would require billing address to be a separate table then.
3. "customers may share the same phone number and billing address"
- Phone number is probably just a column in the customer table, so sharing should be irrelevant
- Sounds like there needs to be a business decision if you want to require the shared billing address to be stored in separate rows or not, this affects maintenance, and if you store the billing address for them together would require special functionality in the application to possibly split them later.
4. "Multiple customers may share the same credit card number"
- Seems straightforward, a customer can have multiple cards, a card can be associated to multiple customers
- "But, the combination of credit cardnumber and billing contact name can uniquely identify a customer" I'm not sure this logically makes sense, since the billing name is associated to the card and there can be multiple customers on the same card, this can't be unique.
5. "A credit card of a customer is associated with a unique payment code."
- Is the payment code unique for each payment made or just unique for each card?
September 14, 2017 at 1:34 pm
Would also be nice if you could show us what you have done so far.
September 15, 2017 at 5:30 am
Your number 2 would guarantee you wouldn't get me as a customer; My Spanish credit and debit cards have my Spanish address as billing address, my British credit and debit cards have my British address. You wouldn't get my wife as a customer either, for the same reason. Maybe someone needs to look at that and any other potential business issues before doing the schema design?
I'm surprised at number 4; that means the person responsable for a credit card's account is sharing the card. Most card issuers frown on that and prefer to issue a separate card with a separate number and a separate name on it; such a card operates against the same credit card account, and with some providers it may have extra restrictions on maximum purchase account per transaction or per time period. And expecting all of a customers cards to carry the same name is just plain silly - some issuers inist on fiull initials and suname, some use first initial and surname, some use first name nd surname, some insist on including a title (Mr, Mrs, Miss, military title, profesional title, whatever) while others don't include titles, and some allow the customer to choose the name on the card (as long as it clearly is a name the customer uses often enough to be recognised as one of his names) - so because the UK has this insane tradition of translating Gaelic names and because the ordering of English christian names is not the same in England and Scotland I could have more than a dozen different names on my cards instead of just the 5 different ones on my current cards, so even if you fixed the issue with you number 2 you would still get neither me nor my wife as customer because of number 4.
.
Tom
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply