The problem is, if I create a getIdFromEntityName() then this won’t work because if I am looking up old tickets then the Entity Name that was valid then, won’t exist in the current Entities table, since it has been updated.
Is there no way this solution can be better? As far as I can tell, all the advice on choosing the Primary Field seems to be to pick something unique (phone number, email address etc) - But the problem with all of these choices is that these do change from time to time. It seems silly to have some customer data that is based on a changeable real-world piece of data, but when it changes in SambaPOS it will break the functionality.
THINKING OUT LOUD…
Looking at the [TicketEntities] table it seems that I can see the link between the TicketID and the EntityID. So what do you think about creating something like this…
function(getBalanceFromTicketIdOrEntityName) {
if (TicketId is NULL) {
// This ticket is not yet committed to the DB, but we know that the EntityName value must be current, so we can safely use EntityName
SQL(getBalanceFromEntityName)
},
{
// This ticket is in the DB, the EntityName may have changed since the ticket was created, so lets find the entityID from the ticketID and subsequently find the account balance
SQL(getBalanceFromTicketID)
}
}
This is obviously not real code, just an outline of what I am thinking. Can you guys see a problem with this approach?