Table description

[Len B]

A few months ago I received very useful help here. At that time, on another
thread
I read an offer to critique future table designs to someone who hadn't done
the
best job on normalizing. Some time in the near future I would like to ask
for
such a critique.

My question here is - What is the preferred way to present my design?

There is already in existence at least one back-end (which has been in use
for
several years) which will provide some tables to link to. The new project
will
have its own back-end as well.

Is it necessary to differentiate where the tables reside?
Is it necessary/advisable to list every field in every table? (eg why list
state/zip)
I know abbreviations PK and FK. Any other shorthand I should use?
Does "a picture = a thousand words" have validity here? eg relationship
shot.

 

[Allen Browne]

If you have a website where you can upload a JPG of your relationship
diagram, feel free to do that and provide the link.

Feel free to only mention crucial fields (though you may want to mention it
if someone may wonder why you omitted something.)

Similarly, mention disparate back ends, but it's not necessary to spell them
out. One crucial aspect is that you will be maintaining referential
integrity manually between tables in different back ends.

One of the crucial things to think through is whether there are any places
where there should be a one-to-many relation rather than assume you can just
place everything in the one table (e.g. if a client may need to have
multiple addresses.) That's the stuff that's quite hard for us to discern
just by looking at your list of tables or relationship diagram.

FWIW, I personally use this (free) utility when trying to analyse someone's
schema:
http://allenbrowne.com/AppRelReport.html
It modifies your relationship diagram so as to provide additional
information about the fields: data type, indexes, and some key properties.
HTH