Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Required info for asking design questions

    I read a lot of posts asking if a design is "good."

    Then the person posts a bare bones schema with little more than table names and column names, and occasionally a 3000x3000 ER diagram that's a mess.

    I'm a big believer in the maxim that once you've stated the question properly you've done 99% of the work of answering it. This is especially true of our line of work, since it's so mathematical in nature.

    So I was wondering if it would be worthwhile for the pros on this site (I can't count myself as one, honestly) to suggest the format for what are very common questions.

    To get the ball rolling, I'd like to see:

    1. Base table names
    2. English predicates
    3. Headers with logical and SQL type information.
    4. A row or two of sample data!

    As an example, a table of addresses might be:

    Code:
    ADRESSES
    /* On Envelope Envelope_ID there is a to Address with Street, Apt, City, State and Zip. Each Address corresponds to an existing Envelope. */
    Envelope_ID INT PK FK(ENVELOPES)
    Street      VARCHAR(80) /* text */
    Apt         VARCHAR(20) /* text */
    City        VARCHAR(40) /* City */
    State       VARCHAR(5)  /* State */
    Zip         CHAR(5)     /* ZIP5 */
    
    Example:
    Envelope_ID Street  Apt City    State   Zip
    1           123 St. 2-F Anytown MA      12345
    Any thoughts? Would that be too much info to wade through when reading a post?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I agree something like that would be useful.

    My pet hate is when posts say something like:

    I have a table A which is of data about employees, and a table B which is of data about departments. Both have a primary key, which for A is let's say EMP_ID and for B is let's say DEPT_ID. There is a 1 to many relationship between A (employees) and B (departments) such that each department may have many employees and each employee is in only one department. This relationship is achieved by a foreign key called EMP_DEPT_ID which is in table A.
    It would be so much easier for the poster (never mind us!) if he/she instead just described the tables like this:
    Code:
    table departments
    ( dept_id int primary key 
    , dept_name varchar2(20)
    );
     
    table employees
    ( emp_id int primary key
    , emp_name varchar2(20)
    , emp_dept_id references departments
    , ... /* Other cols */
    );
    If we could come up with a guideline for new posters, it could only help.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice thoughts, gentlemen, but probably futile

    it's impossible to get new posters to read posting instructions

    besides, if they were to post syntax instead of narrative, what if they've made a serious blunder in the design of the tables? at least with the narrative we have half a chance of understanding what they're doing

    p.s. dude, what's up with varchar2? what's wrong with varchar?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    nice thoughts, gentlemen, but probably futile

    it's impossible to get new posters to read posting instructions
    You are probably right

    Quote Originally Posted by r937
    p.s. dude, what's up with varchar2? what's wrong with varchar?
    Ah, my Oracle bias is showing again. Oracle has the standard VARCHAR, but although it is currently identical to its proprietary VARCHAR2, we are strongly discouraged from using it as it allegedly may change in future. I can't type VARCHAR without wanting to append a 2.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    <i>Oracle has the standard VARCHAR, but although it is currently identical to its proprietary VARCHAR2, we are strongly discouraged from using it as it allegedly may change in future.</i>

    Sniff... I remember back when I was first playing with Filemaker and you didn't have CHAR, VARCHAR, NCHAR, CLOB and all that.

    It was just "Text." And you could put a field constraint on it if you wanted to keep it short.

    Quote Originally Posted by rudy
    besides, if they were to post syntax instead of narrative, what if they've made a serious blunder in the design of the tables? at least with the narrative we have half a chance of understanding what they're doing
    That's why they ought to have English predicates.

    If the forums gods approved, they could redirect first time posters to a page. Or they could even use some simple Javascript to have a button paste a design question template straight into the input field. Nothing more than:

    Code:
    <input value="Design Template" onclick="document.forms.post.elements.message.value='Blah blah blah';">
    It's fill in the blanks after that.

    And let's not rule out electric shocks and thumb screws.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    How do you do the thumb screws remotely?

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    substantiate screws.remote()

    temp = screws.turn()

    go
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Do we risk another sticky just to show how to format a problem?

  9. #9
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Pat Phelan
    How do you do the thumb screws remotely?
    Uh, probably with DCOM. But coding that would be as bad as putting the thumbscrews on...

  10. #10
    Join Date
    Dec 2004
    Posts
    54

    Good Design

    Hi,
    Well, here's my $0.02
    Many times the questions are complete -
    Logical models don't have 'tables', they have business entities.
    Logical models don't have 'columns', they have attributes.

    Many of the times, the answers I provide aren't much more than conceptual models. But in my mind one of the biggest mistakes is 'not getting the basics'

    What really are the entities?
    What are their relationships?

    If you don't get that right... the rest is going to be shaky.

    There U Go.
    Vmusic

Posting Permissions

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