Required info for asking design questions

sco08y · #1 (**permalink**) 01-25-05, 00:56

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?

andrewst · #2 (**permalink**) 01-25-05, 07:34

I agree something like that would be useful.

My pet hate is when posts say something like:

Quote:

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.

r937 · #3 (**permalink**) 01-25-05, 07:49

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?

andrewst · #4 (**permalink**) 01-25-05, 08:05

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.

sco08y · #5 (**permalink**) 01-25-05, 20:44

<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.

Pat Phelan · #6 (**permalink**) 01-25-05, 23:21

How do you do the thumb screws remotely?

-PatP

r937 · #7 (**permalink**) 01-25-05, 23:30

substantiate screws.remote()

temp = screws.turn()

go

certus · #8 (**permalink**) 01-26-05, 02:05

Do we risk another sticky just to show how to format a problem?

sco08y · #9 (**permalink**) 01-26-05, 13:44

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...

Vmusic · #10 (**permalink**) 01-27-05, 00:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech