Is this a correct schema design for what I need to accomplish?

Pawnshop business model:

CLIENTES (customer table), LOTES开发者_开发知识库 (lot table), ARTICULOS (item table) and TRANSACCIONES (transaction table).

The reason I defined a lot table is because when customers pawn or sell items, the pawnshop groups all these items into one lot, calculates the total loan or purchase amount, stores these values under one transaction and prints the ticket with a description of all the items and total amount. So I want the ability to say, if customer defaults on interest payments or does not redeem pawn, then customer forfeits all items and pawnshop may choose to sell some items to gold refinery and/or transfer other non-gold items to inventory to sell to the public. In other words, I want the ability to do a break-out explosions of each item.

Would the above ER be adequate for this capability?

---

From the point of view of a logical model, you probably don't want store_id on the lot (as it comes from the customer) or the transactions or articles (as they get it through the lot and customer). At the physical level you might have those as attributes (called denormalisation), you have the risk of data showing, for example, LOT 1234 being on CUSTOMER C12 and at STORE S1, while the customer table has C12 being at store S2.

Of course it is possible that you allow Mr Smith to pawn an item at one store but make payments on it at another. Or perhaps an item might be pawned at one store but physically relocated to a different one for security or space reasons. If so, then it is appropriate to have distinct store ids on these entities.

However that doesn't sit comfortably with the 'store' being an attribute of the customer, since that implies they have a relationship with only one store.

Also consider what happens if MR P BROKER has three stores, but decides to close one and move the business to one of the others. You need to merge the stores but do you update the store id on all the transactions and articles and lots (including ones that are 'in progress' and those redeemed) or do you leave them with the original store id ?

Another common data modelling issue is identifying customers. Is Mr Smith one customer and Mrs Smith another, or can Mr and Mrs Smith be 'parts' of the same customer ? If Mr Smith pawns something, can Mrs Smith redeem it ? I'm thinking family squabbles, disputed heirlooms.... Perhaps she can't redeem it, but can make payments on it.

If an item (eg a watch) is included in one lot, then redeemed, then included in a different lot, does it get a different item_id ?

---

When a client buys an article offered to the general public, is that a transaction? Or does your database only track transactions about lots?

Can an item exist in your system without being part of any lot? You can't express that fact in the ER model you've presented.

Your ER model doesn't show any many to many relationships. That makes me suspicious. I've never worked in a pawnshop, so I can't say for sure. But every other enterprise database I've ever seen has at least one many-to-many relationship. Sometimes a relationship is treated as though it were an entity, and appears with a box of its own. But that box would be on the "infinity" end of more than one relationship, something I don't see in your diagram.

Buena suerte.