Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to create a conceptual and logical (normalized) models of parking house according to the requirements below. It looks to me as a very simple concept that doesn't need all tables to have relationships - but then they could not be modelled as entities. I tried asking this on stackoverflow but got no feedback for couple of days now.

  1. Three possible methods of payment:

    • a ticket paid on leave,
    • prepaid card with cash credit,
    • prepaid card with "time credit",

  2. Price of ticket depends on time:

    1. 1-2hrs = $0,
    2. 3hrs = $2,
    3. 4hrs = $4,
    4. afterwards x hrs = $(x+1), but max. $20 for 24hrs (... easiest to put these to 24 rows, right?).

  3. A ticket (a) may be allowed a 20% discount (ie. for shopping in the mall).

  4. Cash credit card uses same prices as tickets but with 40% discount.
  5. Cash credit card can be reloaded.
  6. Time card is paid once and allows parking while valid.

The problem is I don't know how to put those highlighted relations to the logical db model and whether event to put them there. Is it ok-practice to have isolated tables in the design?

share|improve this question

1 Answer

up vote 0 down vote

In a transactional system you will want to record the values that applied to the transaction at the time it happened.

It is a mistake to try to use relationships to connect a transaction to a date-depenendent lookup data. The reason for this is that changes to the date-dependent data that happen after the transaction will restate what should have happened to the transaction. This will cause confusion and errors.

The way to handle this type of situation is to denormalize the actual value applied to the payment transaction. Your model doesn't reflect this, but if you had named categories for your discounts and timespan rates, you could keep a relationship from the transaction to the category name while still denormalizing the actual (time sensitive) rate/percent.

EDIT: What should the logical model look like?

In your logical model, the issue is that you need multiple discount (or price) rates because they change over time - or just because there may be multiple rules, for example one price for regular customers and one for special customers, etc.

What is missing is the notion of a rate table. This is like a header which then has rate details attached to it. What you have in your model now is really just the rate detail. You need to insert the rate table header in between the transaction and the rate detail. In your logical model, the relationship is between the transaction and the rate table header. This is OK because the rate table is more stable than the details which change over time.

In your physical model you still need to denormalize make a point in time snapshot of the actual rate used into your transaction because that is the only reliable way to make sure that editing the rates won't restate history.

share|improve this answer
Thank you very much Joel, I got your point, you are right. I don't want to keep relationship for the transaction. – JankoHrasko Jan 16 at 16:56
...at all but I want to denote that table is used for pricing only when the cost is being calculated, although no relationship is maintained in actual implementation of DB. I'm ordered to deliver conceptual and norm. logical model and in conceptual I cant have isolated entities by the definition-that's what puzzles me.I don't know whether to left out those rate entities completely or keep them somehow in the model.I want to keep tables for prices in DB without any relations to them but I don't know if I can have them isolated in the design.Could you please advise me on how to design the model? – JankoHrasko Jan 16 at 17:31
@JankoHrasko - Please see the edit to my answer. I hope that will make it clearer. – Joel Brown Jan 16 at 22:15
@JoelBrown: You said, "The way to handle this type of situation is to denormalize the actual value applied to the payment transaction." You're right about what to do, but not about the terminology. Identicial values used with different meanings isn't denormalized. – Mike Sherrill 'Catcall' Jan 17 at 15:33
@Catcall - granted, however, it is "denormalize-ish" insofar as a value is copied across a relationship from what is kind of like a parent to what is kind of like a child. The fact that the copied value is semantically different because it may become non-redundant over time is critically important but a little on the subtle side. I was in a hurry and got sloppy with my language. – Joel Brown Jan 18 at 3:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.