Database shape?

[wossname]

Database shape?
Hi

I'm in the middle of writing a database program for someone's CD collection.

The database should be able to store the title of the CD, the artist, and the names of all the tracks on the CD (even if I don't know the number of tracks on each individual CD.

I'm not sure how to design the table structure for this database (its just a simple Jet 3.51 DB and I'm using only the references to access the data i.e. not using the DAO control).

I'm not allowed to set all the records to have say 100 tracks as default because that would obviously inflate the DB massively. Does anyone know how i can achieve this with this kind of DB. I can use SQL statements as normal in Jet 3.51, but the actual SQL Server application is not available, nor are ODBC databases.

So I need someone to explain the shape of the DB to me since I cant really visualise it at the moment.

Thanks.

[parksie]

You could try something like

CD
------
ID
Name
Artist
...all the rest of the CD info

Track
-------
ID
CDID <-- the ID of the CD it's part of
Length
Name
....any other info

[wossname]

I'd thought about doing it that way, but I'm not sure how to go about assigning Unique ID's to things without ending up with duplicates. Is there a feature to do this in the VisData add-in?

[parksie]

If you set the ID as the primary key, then Jet will prevent duplicates I'm not sure how to do it in VisData, but you need the ID fields to be AutoNumber, Indexed (No Duplicates).

[wossname]

Ok.

I've created the 2 tables, but I'm having to link the ID's myself (between the tables I mean, the album database is creating them ok).

Right after I add a record to the Album table, I need to find out the ID that was given to that record, so I can write it into each of the Track's record in the other table.

here is the code I'm using so far...

rstAlbums.AddNew
rstAlbums!CDTitle = NewTitle
rstAlbums!Artist = NewArtist
rstAlbums!Category = NewCategory
rstAlbums.Update

'i wanted this next line to grab the AlbumID value for the record I have just written, but it seems that the program forgets which record it has just written!
mLng_CDID = rstAlbums!AlbumID

For i = 0 To UBound(NewTracks())
rstTracks.AddNew
rstTracks!TrackName = NewTracks(i)
rstTracks!CDID = mLng_CDID
rstTracks.Update
Next i

"rst" is my prefix for a recordset object. The Recordsets are assigned earlier in the program

My new problem is this: How do I retrieve the value in the AlbumID field of the record I have just written to the Albums table?

Do I need to use bookmarks? if so can someone explain that to me, cos i is a reel reetard wen it comes to dayterbases!!!

(when it comes to many things actually)

[Jethro]

Posted a complete CD collection data schema on here somewhere. Do you know about data normalisation?

[wossname]

No, I don't. Care to elaborate?

[Jethro]

No one really cares that your CD collection contains more Boy Band albums than Boy George could poke a stick at

[wossname]

What was that for? I was only asking you to go into some detail about the thing that you mentioned since I have no knowledge of it.

Yes, I suppose D-12 is a boyband, as are U2, the Stones, REM and NWA. Yes I think you are right, my entire CD collection seems to be composed of blokes.

Perhaps you should learn to read before you start having a go at people.

,,!,

[Jethro]

Originally posted by wossname
No, I don't. Care to elaborate?

Previous post was a joke Joyce <- think that is a Monty Python line, but confused another pom so maybe is an oz thing????

Ok normalisation:

1st Normal form

Just gather all the possible data fields you think you are going to need. CD Title, Artist, Tracks etc etc etc.

2nd Normal form

Group the data items into Tables. Decide which fields hang together, e.g Artist, Instrument, Nationality etc

3rd Normal form

Review the tables. Will fields be repeated, are they logically setup in Tables., e.g Album Title should only show on one row, rather than on every row of say a Track table. This may lead to further tables being defined.

4th Normal form

Decide on unique keys, foriegn keys, and indexes.

At this stage you should be able to produce a Database Schema, (graphical layout of the database structure).

Any help? There are also 5th and 6th normal forms, but fell asleep when they covered that.

[stevess]

Can you create Foreign Keys/Joins/Integrity Constraints with VisData?

In case you havn't noticed, not too many people use VisData.

[Jethro]

Originally posted by stevess
Can you create Foreign Keys/Joins/Integrity Constraints with VisData?

In case you havn't noticed, not too many people use VisData.

Naw, l use Access 2000 or 97. All database handling is through code using class modules.But that's another story.