2

So a domain expert wants the following relation:

CarOwners(owner_id, name, address, car_1_registration, car_2_registration, car_3_registration, car_4_registration)

The reason given that most people are unlikely to have more cars than that, and that it's easier to reason with than the general case of an arbitrary number of cars. This seems like a common pattern for Excel jockeys, whereas for a DBA it would be much easier to simply have a separate relation:

Car(registration, owner_id)

But this idea is hard to sell to anyone who doesn't know what a JOIN is.

I looked in database literature and couldn't find any references to this (arguably anti-)pattern. Perhaps there's nothing theoretically wrong with it (it's just impractical), or it's simply too goofy to even be considered by academics. But it would be interesting to hear if it's actually covered.

0

(it's just impractical)

Interesting, the purpose might be served. Smart Technology for the Smart people. Database comes to play in his roles due to overcome these issue.

The main problem with this is, it's not extensible If 'car_5_registration' comes on the floor.

  • 1
    That answer seems plausible. It probably wouldn't occur to anyone who's actually into database theory to go to extra lengths for a worse schema. But I don't quite understand what you mean by the second paragraph. – Salvatore Shiggerino Mar 12 '18 at 14:42
1

The Domain Expert has given you a requirement disguised as an implementation detail. I would implement this by:

  • Implementing a relation table as you want
  • Implementing a view which will give up to 4 associated registrations per owner

When an owner registers a fifth registration the system should keep working with no problem (which is probably what the DE wants) but the view he uses for reporting will still only show 4 registrations. (Maybe the view could include the total number of registrations per owner as well?)

This way you may have to change the view (used only for reporting) every now and then, rather than have to change a major table and all the relations and code that will be affected.

  • +1 for adroitly giving the DE whatt the DE needs. – Walter Mitty Mar 8 '18 at 16:37
  • 1
    The advice is absolutely correct, but it's not an answer to the question, I'm afraid. – Salvatore Shiggerino Mar 12 '18 at 14:40
  • @SalvatoreShiggerino - fair enough. Incidentally, implementing the relation table as you wanted is called 'Third Normal Form', and all RdBMS's should have their data in 3NF. Not having data in 3NF (as the DE requested) is called 'un-normalised' and has all sorts of (usually dire) consequences. – simon at rcl Mar 12 '18 at 16:33
  • With the assumption that every man has four cars numbered 1 through 4, I don't see a clear conflict with 3NF. On the other hand I can see it conforming to the letter but not the spirit of the law for 1NF in that you can't have something like a comma-separated list of car license plate numbers in one of your columns. The only real advantage of using separate columns is that you no longer have to call SPLIT_PART() to get a specific car, but your queries will still be a mess, and adding more cars will be an even bigger mess. – Salvatore Shiggerino Mar 13 '18 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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