Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Red face Unanswered: Need help on primary keys

    Hi all,

    I have created a relational database design using unique primary keys and foreign key relationships, however, the client is really angry and wants everything in flat file type formats without primary keys.

    I remember an article by Tom Kyte that was on his asktom.oracle.com site where someone asked about the use of "smart" keys (effectively concatenated values) as apposed to primary keys with unique keys. It was an excellent explanation where Tom sides with the relational design and blatently tells the business they are stupid.

    I want that article. If anyone can find it, or point me in the right direction I would really appreciate it! I think if it comes from Tom, I might be able to persuade them.

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Hi, I feel your pain
    Did the client list, in the original business agreement, that the data should be stored in flat files? Is there anywhere in the agreed contract that grants the client control over the design? Is there an agreement at all? When you gathered the requirements, was a flat file format discussed? Does the client understand what a relational database is all about? Does he/she understand Normalization and has decided to take a different route for a reason? Is this an OLTP system or a DW implementation? I would advise you not to try to tell the customer that they are being stupid, but rather try to persuade them that they are losing the functionality and power of their db if they decide they want flat files and show them that they can still get all the data they want/need. You could even build views on top of the tables to emulate this flattened out structure that the business has asked for.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But, what does client know about structure that is behind the scene? All he/she cares about is how forms and reports look like (and that's what they might be angry with, if you fail to create a report they need).

    Also, you might try to suggest your customer to use an Excel workbook with several sheets and, in several months, end up in a shit.

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I have produced all the required forms and reports and he was very very happy with it. But he likes to tinker and has himself a copy of toad. He logged into the db. He basically says I must make the tables his way or I don't get paid, even though it creates all the outputs as specified in the functional spec.

    Although he is demanding this, he can be persuaded as long as it comes from a credible source. He knows of Tom Kyte and I recon if I can get that article, I can convince him that what he is doing is wrong.

    By the way, I have already suggested using views to provide the data the way he wants it, but he refuses. "I deal with tables, not views".... duh? I don't get it.

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    Dude, this guy is clearly an idiot. I have to ask again tho'. Is there a written agreement as to the scope of the work?

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Yip. Agreement is in terms of system inputs and outputs, which I believe I have provided, and which he seemed to thing I had provided it until he logged into the database.

    At the end of the day, if he really wants it, I'll give it to him.... after all he is the client and the database is not going to cause any bodily harm to anyone (that I know of).

    I can see his other systems that he has done the same thing on and it's a nightmare because they edit the data and then nothing ties up. We have to work some serious magic to prevent the front ends from crashing when the data no longer links up correctly.

    Hey.... he pays per hour though

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Quote Originally Posted by pablolee
    Hi, I feel your pain
    I would advise you not to try to tell the customer that they are being stupid, but rather try to persuade them that they are losing the functionality
    Pablolee, that's probably some good advice I think I'm going to hang onto!

    Yes I have an agreement which is based on inputs and outputs. The system uses Oracle Forms and Reports over the Oracle database. My problem is that the client likes to dig around in the DB and run adhoc sql. I think thats why he wants the "flat file" format... it makes it easier to write the sql to some extent.

    I have tried offering views, but because he uses toad, he wants them to appear under the tables tab.

    Just by the way... the client is an engineer... and I think someone might have done something wrong concerning primary keys in the past (like forgetting to apply the unique key over the business data) and now he is anti primary keys.

  8. #8
    Join Date
    Dec 2007
    Posts
    253

    Tell him the risks

    Well, if he's going to pay you to redesign the system, I suppose you could make that fly, I would however make sure that I supplied him with a document detailing the risk involved in rewriting the system form a well designed structure into what sounds like an idiotic structure just so that he can understand it. Things like No data integrity, increased storage requirements, probable decrease in the systems capability. Non-scaleability, increased development time for future changes, lack of ability to create ad hoc reports (many of these are guesses on my part, you'd need to support any of these claims with evidence based on his requirements) Make sure that he knows that development time for 'his' design would be expensive.
    Bear in mind that you are putting your name to this product and it could have repercussions for you and your reputation i the future. Personally, I would fight tooth and nail to avoid doing this his way.
    Caveat: All this is based on your fairly vague description of what the client wants and what you have supplied and therefore my opinion may change based on a greater level of knowledge of the above info.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dayneo
    ... and I think someone might have done something wrong concerning primary keys in the past (like forgetting to apply the unique key over the business data) and now he is anti primary keys.
    a rookie mistake that we see All. The. Time.

    he's really anti-surrogates but doesn't know it

    and i would ditch this client As. Soon. As. Possible.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Good advise I think. Thanks

  11. #11
    Join Date
    Dec 2007
    Posts
    253
    and I think someone might have done something wrong concerning primary keys in the past (like forgetting to apply the unique key over the business data) and now he is anti primary keys.
    My bet would be that someone chose an innappropriate PK which caused all sorts of issues when the value had to be changed.

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    Quote Originally Posted by r937
    he's really anti-surrogates but doesn't know it
    hahaha... true. But that doesn't computer with 90% of db developers anyway. I mentioned it at our local developer forum and no one knew what I was talking about.

    I think oracle should actually change their terminology from primary key to surrogate key cos 10 to 1 that is actually what it is. But I suppose from Oracles perspective it is a primary key...

  13. #13
    Join Date
    Dec 2007
    Posts
    253
    Nobody knew what a surrogate key was? That would concern me as to the knowledge level of the designers there. Surrogate keys are not an Oracle invention. They are prevalent through all RDBMSs as they are a concept of the Design and modelling of a database.
    oracle should actually change their terminology from primary key to surrogate key cos 10 to 1 that is actually what it is
    Hmm, maybe in some dbs but I think that your statement is a bit (wildly) generalised. Surrogate or Natural makes no difference. A Primary Key is a Primary Key is a... and is dictated by the ddl.

  14. #14
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Quote Originally Posted by pablolee
    Hmm, maybe in some dbs but I think that your statement is a bit (wildly) generalised. Surrogate or Natural makes no difference. A Primary Key is a Primary Key is a... and is dictated by the ddl.
    Agreed... maybe I was getting a little carried away...

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dayneo
    I mentioned it at our local developer forum and no one knew what I was talking about.
    then they are rubes

    nothing wrong with surrogate primary keys, used properly

    used all the time, by rote, and especially without a unique constraint on an alternate key, surrogate primary keys are the mark of naïveté
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •