On Linking Databases

[Konstantinos]

Asking for advise on the following.

Suppose you want to build a database for handling some of your personal
collections say for instance, your music collection, and your pictures
collection.

During conceptual modeling, you realise that there are three distinct
submodels in this design. One for the musical entities (albums and tracks),
one for the pictures, and one for the optical media and the packages that
you use to archive this collection.

Lets name them: subDB 1: Music
subDB2: Pictures
subDB3: Optical Storage

I see three ways of working over this

1: Create a larger database MyCollection.db that contains all 3 submodels
2: Create 2 databases Music.db and Pictures.db each of which contains the
Optical Storage submodel.

3: Create 3 databases Music.db, Pictures.db and OpticalStorage.db where
Music.db is linked with OpticalStorage.db and so is the Pictures.db

Which do you consider the most practical, versatile, error-free etc etc
option?

Regards,

Konstantinos

[Salad]

Konstantinos wrote:

Asking for advise on the following.

Suppose you want to build a database for handling some of your personal
collections say for instance, your music collection, and your pictures
collection.

During conceptual modeling, you realise that there are three distinct
submodels in this design. One for the musical entities (albums and tracks),
one for the pictures, and one for the optical media and the packages that
you use to archive this collection.

Lets name them: subDB 1: Music
subDB2: Pictures
subDB3: Optical Storage

I see three ways of working over this

1: Create a larger database MyCollection.db that contains all 3 submodels
2: Create 2 databases Music.db and Pictures.db each of which contains the
Optical Storage submodel.

3: Create 3 databases Music.db, Pictures.db and OpticalStorage.db where
Music.db is linked with OpticalStorage.db and so is the Pictures.db

Which do you consider the most practical, versatile, error-free etc etc
option?

Regards,

Konstantinos

I would say it depends on what data you want to store about the fields.
For example, music may have artist, Album, song, lengthofsong, genre,
etc. A picture may have a subject, and genre (sunsets, work,
portraits). There isn't much to link the two together. So I'd have 2
tables in this case.

You could create a table that holds a type like "M" for music and "P"
for photos and then link off that to separtate tables for each type of
collection.

I think you'd be better off having a table for each type of
collection...unless the field data is mostly the same amoungst all
collections.

[Konstantinos]

Thanks for your comments but I dont think you understood it well, or maybe I
explained it poorly.
Music Collection and Picture Collection are both complete databases, each
with its own tables and relationships.
A subset of their tables and relations, however, is common, and that is the
one that has to do with the optical media (cds and dvds) that hold these
collections.
So, I was wondering if I could
Create 3 databases Music.db, Pictures.db and OpticalStorage.db where
Music.db is linked with OpticalStorage.db and so is the Pictures.db

I havnt worked with linked dbs before, so I am not sure this is possible.

Regards,

Konstantinos

"Salad" <oi*@vinegar.com> wrote in message
news:_J***************@newsread3.news.pas.earthlin k.net...

Konstantinos wrote:

Asking for advise on the following.

Suppose you want to build a database for handling some of your personal
collections say for instance, your music collection, and your pictures
collection.

During conceptual modeling, you realise that there are three distinct
submodels in this design. One for the musical entities (albums and
tracks), one for the pictures, and one for the optical media and the
packages that you use to archive this collection.

Lets name them: subDB 1: Music
subDB2: Pictures
subDB3: Optical Storage

I see three ways of working over this

1: Create a larger database MyCollection.db that contains all 3 submodels
2: Create 2 databases Music.db and Pictures.db each of which contains the
Optical Storage submodel.

3: Create 3 databases Music.db, Pictures.db and OpticalStorage.db where
Music.db is linked with OpticalStorage.db and so is the Pictures.db

Which do you consider the most practical, versatile, error-free etc etc
option?

Regards,

Konstantinos

I would say it depends on what data you want to store about the fields.
For example, music may have artist, Album, song, lengthofsong, genre, etc.
A picture may have a subject, and genre (sunsets, work, portraits). There
isn't much to link the two together. So I'd have 2 tables in this case.

You could create a table that holds a type like "M" for music and "P" for
photos and then link off that to separtate tables for each type of
collection.

I think you'd be better off having a table for each type of
collection...unless the field data is mostly the same amoungst all
collections.

[Deano]

Konstantinos wrote:

Asking for advise on the following.

Suppose you want to build a database for handling some of your
personal collections say for instance, your music collection, and
your pictures collection.

During conceptual modeling, you realise that there are three distinct
submodels in this design. One for the musical entities (albums and
tracks), one for the pictures, and one for the optical media and the
packages that you use to archive this collection.

Lets name them: subDB 1: Music
subDB2: Pictures
subDB3: Optical Storage

I see three ways of working over this

1: Create a larger database MyCollection.db that contains all 3
submodels 2: Create 2 databases Music.db and Pictures.db each of
which contains the Optical Storage submodel.

3: Create 3 databases Music.db, Pictures.db and OpticalStorage.db
where Music.db is linked with OpticalStorage.db and so is the
Pictures.db

Which do you consider the most practical, versatile, error-free etc
etc option?

Regards,

Konstantinos

If you need to maintain RI between Music & Optical Storage and Pictures &
Optical Storage then I don't think they can be separate. So maybe option 2
would be better. Or option 1 and split it.

[Deano]

BTW I spent over an hour yesterday reading an excellent thread on RI via
Google Groups. 115 posts but recommended.

http://makeashorterlink.com/?D422213A9 (leads to Google Groups archive
posting).