I have decided to rewrite a couple of applications that use my
personal information database and I'm looking for ideas on how to
structure a new database for them.

For most purposes I want to communicate with a family or business at
its "home" address. For example, "Fred and Betty Bloggs" or "Acme
Widgets" have a specified street address and telephone number. It is
possible that the Bloggs family has a holiday house or I have to deal
with Acme Widgets at more than one location. I also need information
about individuals such as mobile phone numbers and birthdays but I
don't want to duplicate shared home and business addresses and
telephone numbers. For example, Betty Bloggs could work for Acme
Widgets and share a town house and a country house with Fred.

Please ignore any database structure implied in the preceding
paragraph. I'm looking for the ideal PIM database structure and you
probably understand the requirements better than I do. No doubt you
have been frustrated by the address books in many applications that
insist you supply all the details for every individual.

My excuse for posting in comp.lang.java.databases is that I follow it
and the applications will be written in Java and use Derby as the
database. I confess that the reason is to canvas a wider audience but
I refuse to admit the post is spam.

On Apr 26, 9:33 am, David Segall <da... (AT) address (DOT) invalid> wrote:
Sorry, but my crystal ball has been broken for quite some time. Only
you know the requirements for your project. What I would suggest is
that you study database modeling for a bit before beginning to write
the program and database. Look up ERD - Entity Relationship Diagrams
Pick a couple sites that are university based and read the materials.

And always keep in mind that sometimes the same requirements may
result in different designs from different developers Some of the
differences will be merely style differences and others will be
functional differences in areas not detailed in the requirements. As
they often say in the PERL programmers group, there's more than one
way to do it.

Finally, to give you a hint, I would approach this by making
addresses a separate entity, Then both person and business entities
can reference the address entity (e.g. allows a home business) I'll
let your discover the entity you'll need to allow multiple addresses.
(it is not hard.)


ERD development is independent of the DBMS as long as the DBMS is
relational. Using JAVA you may fall into some of the Object Oriented
thinking that sometimes corrupts the relational design. So think
about your DB design carefully.

HTH,
Ed

On Apr 26, 9:33 am, David Segall <da... (AT) address (DOT) invalid> wrote:
Sorry, but my crystal ball has been broken for quite some time. Only
you know the requirements for your project. What I would suggest is
that you study database modeling for a bit before beginning to write
the program and database. Look up ERD - Entity Relationship Diagrams
Pick a couple sites that are university based and read the materials.

And always keep in mind that sometimes the same requirements may
result in different designs from different developers Some of the
differences will be merely style differences and others will be
functional differences in areas not detailed in the requirements. As
they often say in the PERL programmers group, there's more than one
way to do it.

Finally, to give you a hint, I would approach this by making
addresses a separate entity, Then both person and business entities
can reference the address entity (e.g. allows a home business) I'll
let your discover the entity you'll need to allow multiple addresses.
(it is not hard.)


ERD development is independent of the DBMS as long as the DBMS is
relational. Using JAVA you may fall into some of the Object Oriented
thinking that sometimes corrupts the relational design. So think
about your DB design carefully.

HTH,
Ed

On Apr 26, 9:33 am, David Segall <da... (AT) address (DOT) invalid> wrote:
Sorry, but my crystal ball has been broken for quite some time. Only
you know the requirements for your project. What I would suggest is
that you study database modeling for a bit before beginning to write
the program and database. Look up ERD - Entity Relationship Diagrams
Pick a couple sites that are university based and read the materials.

And always keep in mind that sometimes the same requirements may
result in different designs from different developers Some of the
differences will be merely style differences and others will be
functional differences in areas not detailed in the requirements. As
they often say in the PERL programmers group, there's more than one
way to do it.

Finally, to give you a hint, I would approach this by making
addresses a separate entity, Then both person and business entities
can reference the address entity (e.g. allows a home business) I'll
let your discover the entity you'll need to allow multiple addresses.
(it is not hard.)


ERD development is independent of the DBMS as long as the DBMS is
relational. Using JAVA you may fall into some of the Object Oriented
thinking that sometimes corrupts the relational design. So think
about your DB design carefully.

HTH,
Ed

Ed Prochak <edprochak (AT) gmail (DOT) com> wrote:

crystal ball to understand my requirements and that you may have some
useful advice. Why are you so reluctant to give it to me?

Ed Prochak <edprochak (AT) gmail (DOT) com> wrote:

crystal ball to understand my requirements and that you may have some
useful advice. Why are you so reluctant to give it to me?

Ed Prochak <edprochak (AT) gmail (DOT) com> wrote:

crystal ball to understand my requirements and that you may have some
useful advice. Why are you so reluctant to give it to me?

On Mon, 28 Apr 2008 18:33:58 +0000, David Segall wrote:

said:

Only you know how you need to relate addresses, phone numbers, etc, etc to
persons and businesses and whether to model relationships between people
and businesses. If you don't know this already, analyse the data you own
until you do understand it.

Then use that analysis to derive an entity-relationship diagram. Add
attributes to the entities. Decide which are keys. Eliminate derived
attributes. Normalise the result to 3rd normal form. Then and only
then, design and code your schema, adding in the indexes needed to support
prime keys and foreign key relationships.

For your reading pleasure: I don't know who the currently fashionable
database design gurus are, but finding and reading stuff by Chris Date,
especially "An Introduction to Database Systems" volume 1, would be a good
start.


--
martin@ | Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot

On Mon, 28 Apr 2008 18:33:58 +0000, David Segall wrote:

said:

Only you know how you need to relate addresses, phone numbers, etc, etc to
persons and businesses and whether to model relationships between people
and businesses. If you don't know this already, analyse the data you own
until you do understand it.

Then use that analysis to derive an entity-relationship diagram. Add
attributes to the entities. Decide which are keys. Eliminate derived
attributes. Normalise the result to 3rd normal form. Then and only
then, design and code your schema, adding in the indexes needed to support
prime keys and foreign key relationships.

For your reading pleasure: I don't know who the currently fashionable
database design gurus are, but finding and reading stuff by Chris Date,
especially "An Introduction to Database Systems" volume 1, would be a good
start.


--
martin@ | Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot

On Mon, 28 Apr 2008 18:33:58 +0000, David Segall wrote:

said:

Only you know how you need to relate addresses, phone numbers, etc, etc to
persons and businesses and whether to model relationships between people
and businesses. If you don't know this already, analyse the data you own
until you do understand it.

Then use that analysis to derive an entity-relationship diagram. Add
attributes to the entities. Decide which are keys. Eliminate derived
attributes. Normalise the result to 3rd normal form. Then and only
then, design and code your schema, adding in the indexes needed to support
prime keys and foreign key relationships.

For your reading pleasure: I don't know who the currently fashionable
database design gurus are, but finding and reading stuff by Chris Date,
especially "An Introduction to Database Systems" volume 1, would be a good
start.


--
martin@ | Martin Gregorie
gregorie. |
org | Zappa fan & glider pilot