Database design? 1

[fenris]

I am designing a database to catalog my mp3 collection. I want to have three tables within the database. One to store the mp3 id3 tag attributes, one to store the physical properties of the mp3 such as bitrate and sample rate and an other to store the file attributes such as file size, date created etc. To explain make explaining this a little easier, I'll assign names to the tables:

Table A: windows file attributes
Table B: mp3 File attributes
Table C: mp3 id3 tag info
Table D: mp3 id3v2 tag info (quite possible in the future, but it will not be in my DB till I can figure out how to get the information)

I want table A to be the "master" table because every mp3 will have windows file attributes. I believe that table A would have a one to many relationship. So with this I want to be able to gather all the information about a particular mp3 and place it in the appropriate tables with a minimum of fuss. I have 31 or so fields to add to the various tables.

My problem is that I am unsure of how to properly enter the data. I would like to put the data in the DB with a minimum of fuss, preferable with sql.

This is what I have been doing. I put the information into table A and then figure out what ID the database has given it, then using that ID I place the information into the other tables. I know this is a round about way of doing things, but it is the only way that I could figure out how to do this.

I am thinking that the tables have to be joined and then the data inputed into the appropriate places?

Any help will be appreciated...



Troy Williams B.Eng.
fenris@hotmail.com

 

[Terpsfan]

You haven't established the necessary relationship between the tables yet. The first thing you need to do is put a foreign key field in TableB with the same data type and data length as the ID field in TableA. Then in the database window click on the relationships icon and add tableA and tableB to the relationships window. Drag the ID field from TableA to the ID field in TableB, declaring referential integrity and cascading updates,deletes. Then create a query with all fields from TableA and TableB. After that create a form adding Fields from TableA first and then Table B. This will create the necessary One to many relationship. This example was using Access entirely. If you want to create a recordset in VB, you'd Make your Datasource equal to the query Select tableAfields,tableBfields FROM TableA INNER JOIN TableB ON TableA.ID=TableB.ID. I hope this helps.

 

[fenris]

The problem is that I want to create the database using code only. That is why I am having a problem. I don't know how to establish a relationship between tables using code only. Currently I can create tables and fields with out any problem. I can also assign primary keys.

Any ideas on how to accomplish this through code?


Troy Williams B.Eng.
fenris@hotmail.com

 

[chiph]

Try:

ALTER TABLE {referencing table name here} ADD FOREIGN KEY {referencing table column here} REFERENCES {primary table here}( {column in that primary table here} );

You need to make sure that the two columns (in the referencing table and primary table) have the same datatype and size. It also helps maintainability if they have the same name.

Chip H.

 

[fenris]

Thanks Chip...

Troy Williams B.Eng.
fenris@hotmail.com

 

[fenris]

I still don't quite understand how this would work. Does anybody have some simple source code and a database with a few tables that could illustrate the point more effectively?




Troy Williams B.Eng.
fenris@hotmail.com

 

[Qaroven]

How bout this (if you're using SQL Server - hopefully you can get the gist anyway)

use master
create database test1
go
use test1
set nocount on
create table Images (ImageID int, ImageText varchar(50), constraint pk_images primary key (ImageID))
create table ImageCategory (ImageID int, CategoryID int, constraint pk_imagecategory primary key (ImageID, CategoryID))
alter table ImageCategory ADD FOREIGN KEY (ImageID) REFERENCES Images (ImageID)
insert into Images (ImageID, ImageText) values (1, 'image1')
insert into ImageCategory (ImageID, CategoryID) values (1, 1)
--delete from Images
--delete from ImageCategory
drop table Images
drop table Categories
drop table ImageCategory
use master
drop database test1

Now if you try to delete from Images before deleting from ImageCategory, you find it won't let you.. Ben
+61 403 395 052

 

[rtshort]

I don't understand this I guess. Why not create them in SQL and use code to update, delete & etc. Isn't it a lot of trouble to create tables in code instead of the GUI in SQL??

 

[Qaroven]

I just prefer that way to keep in practice .. and as I've mentioned elsewhere I have issues with the amount of times Enterprise Manager crashes. Ben
+61 403 395 052

 

[fenris]

Here is what I did and it seems to work, the only problem is that the mp3ID fields don't line up.



'----------------------
Dim sql As String
Dim current As clsMP3Prop
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


sql = "SELECT * from mp3Info INNER JOIN fileInfo ON mp3Info.mp3ID = fileInfo.mp3ID"
rs.Open sql, DB, adOpenStatic, adLockOptimistic

For Each current In toAdd
With rs
.AddNew

!diskID = current.getDiskID
!volLabel = current.getVolLabel
!volSerial = current.getVolSerial
!diskType = current.getDiskType
!Path = current.getPath
!fileSize = current.getFileSize
!FileName = current.getFileName
!created = current.getCreated
!lastAccessed = current.getLastAccessed
!lastModified = current.getLastModified
!timesDownloaded = current.getDownLoaded

If current.getHasTag Then
'.Append "mp3ID", adInteger
!Title = current.getTitle
!Artist = current.getArtist
!Album = current.getAlbum
!Year = current.getMp3Year
!Comments = current.getComments
!Genre = current.getGenre
!FileBytes = current.getMp3Size
!Seconds = current.getLength
!MpegVersion = current.getMpegVer
!layer = current.getLayer
!bitRate = current.getBitRate
!Frames = current.getFrames
!Hz = current.getSampleRate
!Mode = current.getMode
!Private = current.getPrivate
!crc = current.getCRC
!copyright = current.getCopyRight
!Original = current.getOriginal
!Emphasis = current.getEmphasis
!Channels = current.getChannels
End If


rs.Update
End With
Next

rs.Close
Set rs = Nothing

'-----------------------------------
The code above puts everything into the right place in the right tables, except the mp3ID column in the mp3Info table doesn't match the corresponding mp3ID in the fileInfo table, it autoincrements. Other then that, it seems to work fine. Any ideas on how to overcome this problem?

Troy Williams B.Eng.
fenris@hotmail.com