Strategy for a databse

[Lars]

Hi group,

I have seen several suggestions in this forum that one should take
enough time to make a good plan for the database, rather than jumping
straight in building tables. But I have not been able to find good
texts on a strategy for the planning.

I have some experience with building a database of people, their
adresses and some other parameters. I constucted queries and reports
and forms. All in all it held well over a thousand people of two
specific kinds, from five different countries and about 8 different
regions. I could merge with Word for labels and letters to selected
targets.

So I am not a total newbie at this, and I know of some mistakes that
would have been better avoiding early in the creation. I understand it
is good to have a strategy. My big book on "Using Access" also tells
me so. But that is about it!

Now I would like to read a text on the subject itself.

I am captain of a 100 ft long high speed passenger boat. Mostly we do
regular trips but often also charters of various lenghts. There are
various numbers of passengers, various crew members. I need to plan
well ahead with ordering fuel, always having enough but not carrying
needless amounts that way the ship down.

For all these things, and many others I have reports, in Excel format,
where timings and numbers need to go.

Plans on trips etc change more often than I would like them to. And I
am getting increasingly tired of updating the same info in several
different Excel books. I think an Access database could serve me well,
but I am unsure of where to start.

I am not looking for something to buy.

Tia

Lars
Stockholm

 

[Brendan Reynolds]

Rebecca Riordan's 'Designing Effective Database Systems' is the best source
I know for this kind of information. Here's a link if you want to check it
out ...

http://www.amazon.co.uk/exec/obidos...16692/sr=1-4/ref=sr_1_2_4/203-5653630-7624753

 

[Klatuu]

There are many volumes of information available on database design and
normalization. You might try Amazon.com or if you have access to a book
store that carries technical books, you might try there.

Database design is as much common sense as anything else. There are some
basic rules that you should be aware of. if you keep those in mind, it will
help as you lay out your design.

First, name carry any data element in more than one place. For example,
let's say you have a number of people who serve as crew on your vessel;
however, not all of them will be on every trip. You do need a table to keep
track of who will be crewing which positions on which trips. You do not want
to have all the details on each crew member for every trip. As you have seen
in Excel, it involves a lot of data entry and it wastes space. So, what you
want is a table that carries all the information you may need to know about
each possible crew member in one table. Each crew record will have a Primary
Key that is unique to that crew and cannot be duplicated.
Then you have a table that identifies each trip. In that table will be
information about the trip that is unique to that trip only. It will also
relate to the crew table using a cross reference table to tell you who is on
the crew for that trip using the crew table's Primary Key. For example:

TripTable
TRIP_ID - Primay Key
TRIP_DATE
TRIP_SAIL_TIME
TRIP_DESTINATION
(other Trip Info fields)

CrewTable
CREW_ID - Primary Key
CREW_LAST_NAME
CREW_FIRST_NAME
(other crew info fields)

TripCrew
TP_ID - Primay Key
TRIP_ID - foreign key to trip table
CREW_ID - foreign key to crew table

The TripCrew table then sits logically between the two tables. It is
relateded to the Trip table using TRIP_ID. This is called a One to Many
relationship. In other words, you can have many crew members for one trip.
The Trip Crew table is needed because one crew can crew on many trips. That
means there is a many to many relationship between trips and crews. Many to
many relationships have to be resolved down to a one to many. Then to get the
individual crew information, the TripCrew table is related to the Crew table
using the CREW_ID.

See how this works? Now we never have to put the crew's name on information
in more than one time regardless of the number of trips. We just use the
relational capabilities to point to the data.

Another important rule to keep in mind is never carry a calculated value in
the database. Calculate it each time you need to use it. This is because it
wastes database space to carry it. It is faster, because almost any memory
calculation is faster than a database fetch, and it is more accurate. There
are things that can happen in a database that may cause one of the values
used in the calculation to change. This will cause the calculated value to
be incorrect.

One last suggestion. The way I usually start to lay out a database design
is with Visio or a pencil and paper. First I identify an entity, draw a
rectangle, and name it. Trip is an entitiy. Then I think about what I will
need to make a trip. I will need a crew, so I draw another rectagle, name
it, and draw a line from the Trip rectangle to the Crew rectangle. Then I
consider the one to many rule. I will make many trips and each trip will have
many crew, but each may also make many trips. Now I need to erase my first
line, add a new rectangle, name it TripCrew, and redraw my lines. The
traditional way to represent the one to many relation ship is to make a
crow's foot at the end of the line on the many side.

Hope this will get you started. Good Luck.

 

[smartsw2006]

Avoid typing the same text again and again
Stop wasting your time on mouse movements
Open favorite web pages with a single hotkey press
Record keystrokes and play them back with a single hotkey press

 

[Lars]

Thank You Klatuu, Terrific!

A lot of hands on advice there. I am sure I will have good use for it.

Is there a typo, or a missing word or comma in your very first
sentence? I can't really understand what it means.

First, name carry any data element in more than one place. For example,
let's say you have a number of people who serve as crew on your vessel;
however, not all of them will be on every trip. You do need a table to keep

Lars
Stockholm

 

[capn samuels]

I am new at this, I have started making some single pages, starting from a
blank page, so far it all looks good after I save each page, My questions
are: after I m,ake the total number of pages I want (approx 5-6) can I turn
those pages into a website, and how do I combine them to do so, also how do I
link text on 1 page to text on another of the pages, can I use the hyperlink
icon to do this with the page #'s ?