Database design help. 1

[bra1niac]

First, let it be known that I am more than competant at dealing with db's in almost any other way aside from the creation.

Scenario:

A database needs to be created to house all things music. The information required: [Band]; [Release, #ofTracks, DateofRelease], [Track#, SongName] - Each bracketed set being a table. Each will have their unique ID's (BandID, ReleaseID, SongID). Everything looks pretty easy so far. Normally the relationship would go from Parent[BandID] -> Children[ReleaseID] -> Children[SongID]

Ok, here's the twist. The anomolies begin when you introduce Live recordings and the like. Compilations, soundtracks or the tape you made of your kid sister singing to her dolls when she wasn't looking.

The point is, you can't have children when there are no parents, right? You have songs, but no releases. Keep in mind, it is unacceptable to insert spacers or irrelevant info in the interim as a work around.

So, the first thought was to build a fourth table that contained only the ID's and this would be how you queried it.

At this point, I don't really know what I'm doing anymore. I have painted myself into the proverbial corner with logic.

Oh, and there is a classification category, and for the sake of this example, we'll call the table Category [MusicType, MusicTypeID] so it removes us even one more step from cheating.

This is my first db that wasn't flatfile, btw. "It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[PamelaG]

Hiya!

Hey, you're doing great so give yourself a pat on the back! The issue here is what we call "business rules". In fact, you CAN have children without parents! In the relationship window, double-click on a relationship line to see the options. You'll notice that "enforce referential integrity" is NOT checked. (You can turn it on if you want to insist on no orphans...).

However, from a design point of view, it is usually better to have all your records clearly in a hierarchy somewhere. In this case, you simply want to add new categories as they arise. Band=kid sister etc. That will keep your data cleaner in the long run and probably eliminate work in the future when your sister forms her own million-selling band and you want to go back and link her doll session song to her latest release.

Pamela

http://www.accessinsight.com

 

[dneufarth]

Have you tried the Music Collection template that comes with Access 97?

Dave

 

[bra1niac]

I was actually using the music as an analogy. I was afraid I wouldn't be clear enough by trying to explain the reasoning for what I am trying to do, therefore I related it to something I didn't have to provide depth for. But yes, I have tried several of the wizards and quickly found the above mentioned flaws (based on my data).

PamelaG: If I understood you correctly, you are saying to setup the parent/child relationships as I diagrammed and not so much as having a central query table (i found a few issues in that as well).

Ok. Based on that, how will I deal will a parent with duplicate grandchildren (Songs, if you will) but missing children (Releases) when the grandchildrens names need to be identical, however, they somehow need to reflect being apart of a separate group (the live release, compilation or soundtrack, standard release) without inserting 'space holding' data or something that isn't relevant?

That's my stumbling block. Almost there.. I know that once I grasp this particular issue, this whole Db concept will become crystal clear for me. "It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[PamelaG]

Hi!

Hey, try me out with your REAL data! That will probably be more productive in the long run. Give me a brief description of the scenario and then the table listing. Believe me, I've seen it all!

As for the wizards, if it were in my power, I would disable that functionality permanently, but that's just my bias!

Pamela

http://www.accessinsight.com

 

[bra1niac]

Ok, lemme see what I can cram in here in the little time left..

We have Directors that manage suppliers who in turn have products. Simple structure. There are tons of exceptions.

Directors can have products assigned without suppliers, however, they still need to be classified (MusicType).

YOu can have duplicate products, but they need to specify what supplier they belong to.

**Suppliers can exist for more than one Director, but the products will be different as each Director handles a different type of product (softwar, transportation, etc)

That, in a nutshell, is it, tho i'm afraid it might be a bit incoherent. That's why I thought the music analogy fit nicely.

FYI - This is a purchasing deptartment. This information is only the basics, but the additional fields don't play a role in organization. (notes, type of payment accepted, etc) "It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[PamelaG]

Hi!

You said:

We have Directors that manage suppliers who in turn have products. Simple structure. There are tons of exceptions.

===========
tblDirectors, tblProducts, tblSuppliers
=============

You said:

Directors can have products assigned without suppliers, however, they still need to be classified (MusicType).

=========

Sounds like Directors have PRODUCTS, which in turn have SUPPLIERS!
============

YOu can have duplicate products, but they need to specify what supplier they belong to.

**Suppliers can exist for more than one Director, but the products will be different as each Director handles a different type of product (softwar, transportation, etc)

========

tblDirectors
------------
DirectorKey
DirectorName etc.

tblProductTypes
---------------
ProductTypeKey
ProductType

tblDirectorProductTypes
------------------------
DirectorProductTypeKey
DirectorKey
ProductTypeKey

tblSuppliers
------------
SupplierKey
SupplierName etc.

tblProducts
-------------------
ProductKey
SupplierKey
ProductName
ProductTypeKey

tblDirectorProducts
--------------------
DirectorProductKey
DirectorKey
ProductKey

Now you can limit directors to only certain types of products, assign them specific products from a given vendor etc. How am I doing?!

Pamela
http;//

http://www.accessinsight.com

 

[bra1niac]

Interesting. It's quite similiar to what I was trying to do, but the divisions with the tables are different, most likely more efficient. Thanks for the help! "It's easier to ask forgiveness than it is to get permission." - Rear Admiral Dr. Grace Hopper

 

[PamelaG]

Hi!

Sorry to be late responding -- I was away from my office all day... You're welcome!

Pamela