Home Forums Database Design Relational Theory A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers RE: A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers

  • Jeff Moden - Tuesday, December 5, 2017 8:05 PM

    So what are the "ex GST amount" and a "GST amount" columns that you speak of?  In particular, what is the difference due to the "ex" in the first column name?

    It's just one possible example, but for context, in Australia many things have goods and services tax applied. More complete names might be "invoice_GST_exclusive_amount" and "invoice_GST_amount". Adding the two together would give the "invoice_GST_inclusive_amount" for the line. They're generally kept separate because the amount excluding GST is company revenue, the GST amount is paid to the government. And not everything is charged GST, so if you just kept a single "amount" value on the line, you would not be able to tell whether or not GST was in fact charged. But of course both must be applied, since the customer has to pay the sum of the two.

    I could use any number of similar examples. If I wanted to model a leg of a journey, for example, I need a start location and an end location. Does my "journey_legs" table therefore have two columns, one for start location and one for end location, or do I have a separate "journey_leg_locations" table, which is in a 2 to 1 cardinality relationship with my legs table, and have an additional type column constrained to "start" and "end"? In this case, the choice *not* to use a "single column" solution seems obvious.