Guidelines: Elements of Creating Tables

[Eric Tubbs]

Greetings,

I hope you all can lend some advice on creating tables in general. The
following information below has been gathered from different locations and I
would like to ask for your help. My question concerns the table element
guidelines. I would like to find out what an example would be of each of the
following, multipart and multivalued fields, and how one would go about to
make sure that these are not present in a table.

Thanks,

Eric


--------------------------------------------------------------------
Guidelines for naming fields
- Unique, descriptive name that is meaningful to the organization
- Accurately identifies the subject of the field
- Minimum number of words
- Do not use abbreviations or acronyms
- No proper names
- Use singular form
- Prefixes

Field Elements
- Represents a characteristic of the subject of the table.
- Contains only a single value
- Cannot be deconstructed into smaller components
- Does not contain a calculated or concatenated value
- Is unique within the entire database structure
- Retains all of its characteristics if it appears in more than one table.

Table Elements
- Represents a single subject
- Has a primary key
- Does not contain multipart fields
- Does not contain mulivalued fields
- Does not contain calculated fields
- Does not contain unnecessary duplicate fields
- Contains only the minimum redundant data
--------------------------------------------------------------------

 

[Gilligan]

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
is a link to a pdf file that discusses multipart and multivalued fields on
page 3.

 

[Eric Tubbs]

Gilligan,

Thank you for the wonderful document. I now know what multi-valued and
multi-part fields are. Question for you if that is ok.

I am currently attempting to create a database and the data is going to come
from a CSV file from an Internet site. A field named STREET_ADDR is defined
to have a maximum of 200 characters (this was not set by me). Since the
address is a multi-part field, how could one break this field up after it
has been imported?

Also, what is your opinion of this book "Database Design for Mere Mortals"?

Thanks for your help,

Eric

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
is a link to a pdf file that discusses multipart and multivalued fields on
page 3.

Greetings,

I hope you all can lend some advice on creating tables in general. The
following information below has been gathered from different locations

and

 

[Gilligan]

Response in line...

Gilligan,

Thank you for the wonderful document. I now know what multi-valued and
multi-part fields are. Question for you if that is ok.

No, multi-valued and multi-part fields violate normalization principles. In
real life they lead to numerous problems.

I am currently attempting to create a database and the data is going to come
from a CSV file from an Internet site. A field named STREET_ADDR is defined
to have a maximum of 200 characters (this was not set by me). Since the
address is a multi-part field, how could one break this field up after it
has been imported?

Breaking up a multi-part field into its component parts tends to be more
complex than it first seems. While a street address may theoretically be a
multi-part field, depending on your requirements it may be perfectly
acceptable to treat it as fully normalized. Will it ever be a requirement
to be able to access the (for example) apartment number, street number,
street name, or street type (Street, Avenue, Boulevard) as an individual
entity. If the application will always treat the street address as an
entity then for practical purposes it is an entity. However, if you must be
able to pull up all records for a particular street then street will have to
be a single entity. Realistically given a sample address of Apt 100-999
Main Street; how often are you going to want to pull all clients who live in
Apt 100 or all clients who life at street address 999? (probably never).
However, you might want to be able to retrieve all clients who life on Main
Street or Ash Street or whatever. Breaking such a field into component
parts tends to be problematic because users have not followed consistent
practices when entering the data and Apt 100-999 Main Street may be entered
as:
Apartment 100-999 Main St.
100-999 Main Street
Apt 100 999 Main Str.
Apt 100 - 999 Main St
Apt 100 - 999 West Main Street
Apt 100 -999 Main Street West
and other variations

In short, in order to break it up into component fields you would have to
figure out the different ways people have entered the data in and then write
code to accomodate all the variations (using functions like left(), right(),
mid() to parse the original string into component parts).

Also, what is your opinion of this book "Database Design for Mere

Mortals"?

Good book as an introduction to Database Design. If you read the Access
newsgroups over time you will see it recommended alot. Another good
database design book is by Rebecca Riordan, Designing Relational
Databases--it is currently out of print, but worth looking for a used copy.

Thanks for your help,

Eric

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
is a link to a pdf file that discusses multipart and multivalued fields on
page 3.

and of
the

about

 

[Gilligan]

Thinking about my comments regarding parsing street addresses, they assumed
a North American address. When you start looking at some foreign street
addresses, parsing into component parts becomes even harder...

 

[Mike Sherrill]

On Tue, 11 Nov 2003 15:49:17 -0800, "Eric Tubbs"

[snip]

Field Elements [snip]
- Cannot be deconstructed into smaller components

Think about the date "17-Nov-2003".

 

[John Vinson]

On Tue, 11 Nov 2003 15:49:17 -0800, "Eric Tubbs"

[snip]

Field Elements [snip]
- Cannot be deconstructed into smaller components

Think about the date "17-Nov-2003".

a philosophical point: 17-Nov-2003 is indeed a valid, unique entity.
It's a day in history (one which will soon be over, never to return);
it has attributes which are not associated with it being the
seventeenth of something, or being part of the eleventh month.

That is, the date cannot be deconstructed without irreprably breaking
it. "He who breaks a thing to find out what it is made of has left the
path of wisdom"...

 

[Mike Sherrill]

a philosophical point: 17-Nov-2003 is indeed a valid, unique entity.

I never said it wasn't.

It's a day in history (one which will soon be over, never to return);
it has attributes which are not associated with it being the
seventeenth of something, or being part of the eleventh month.
Right.

That is, the date cannot be deconstructed without irreprably breaking
it.

The parts are not the whole, but I can extract the year 2003 from the
date 17-Nov-2003 without breaking 17-Nov-2003. The point,
philosophical or not, is that the OP is wrong. The relational model
permits values of arbitrary complexity (a high falutin' way of saying
that such values can be deconstructed into smaller components). It
just requires that the dbms--not the user, not applications--be
responsible for extracting the parts.