How is Susie's business diagrammed?
ERD
Susie sells seashells by the Colorado sea shore. She's contacted us, BL Inc., to make a database to support her online sales. Susie tracks the name and address of each customer, but she wants things to be simple so she has asked us to keep each as a single field.
Customers have at least one way of paying (payment source), and sometimes more. Checking, credit and debit cards, gold coins smuggled across the Wyoming border...they all work as do many other ways of paying. She wants to track the payment type, and - where appropriate - the account number, expiration date, bank name and bank phone for each payment source. Every payment source is associated with just one customer.
Payment sources are used to pay for orders, and orders have one or more inventory items on them. Payment sources may be added prior to actually being used to make a payment. It is important to know how many of each item is on each order. Inventory items might show up on multiple orders, though some items are new and haven't yet been ordered. All orders have at least a small payment made on them immediately.
She keeps a record of the item name and description for each seashell in her inventory. For the orders she tracks the order date, and the date the order shipped.
Here's the deal, though. There's not much seashore in Colorado, so her seashells are expensive. Sometimes an order must be paid for with multiple payment sources. Sometimes one order is paid off with the same payment source, but over time. For each payment, Susie wants to track the payment date and payment amount. She warned me that she doesn't want any payment information to get lost over time.
Customers also have one or more online accounts (the seashell business isn't totally legal). Online accounts are always owned by just one customer. Susie needs to capture the username, password and email for each account (usernames aren't unique). Sometimes those Wyoming border crossings don't work out due to the frozen bunny zombies. When a customer is deleted from the world of the living (and the database), their online account should be automatically deleted as well.