Thanks ceh,
I've been aiming for normalisation though obviously I've not got it right. It's certainly worth reading about it from another angle to try and get it to sink in!
Basically, there's only two kinds of textual data in this db. The rest is about using additional tables to try and logically connect them in an appropriate manner so I can get the information I want out.
Maybe some context will help. I've got thousands of sound files differing from each other in that each contains a name sung in one of (currently) 12 styles. The files are named using one spelling of the sung name (though other spellings of the name also need to refer to the same file). The actual files for each style are kept in a directory of the name of the style.
Not all names are available in every style.
Not all names are linked to file-names.
[edit]Maybe it would be sensible to add here what I mainly what the db to do!!
Given a name and a style, the db needs to give me an associated filename (if there is one) and to tell me if that filename exists in the given style.
I've actually got this working with my first attempt (in a work-aroundy way heavily relying on QuicKeys to trigger my queries) but I've got a boolean column for each style in my filenames table which is not normalised. Also I'm stuck when it comes to instances where everything is not hunky-dory. e.g. name not in db, name not associated to filename, no file available in requested style etc.
[/edit]
So...
The two tables with their fields for the basic text info. are:
tblNames.NameID
tblNames.Name
Only one entry for each name is permitted.
tblStyles.StyleID
tblStyle.Style
Only once should a particular style be entered.
In order to limit the number of sound files, only one file of each name for each style is permitted (regardless of the number of spellings) - and it makes sense that the spelling used is the same in every style.
That to me implies I need a third main table with a difinitive list of allowed file names:
tblFileNames.FileNameID
tblFileNames.FileName_NameID
My logic says though the file names are referring to a different entity (files not people), because they are the same names, they may as well refer to the names table.
This table should also not permit duplicates.
My aim is to to be as strict as possible in what's allowed so that it's harder to mess up the data by repeating e.g. putting in a name again if a user can't be bothered to check if it already exists (and therefore having to link it again to a file name etc.) That's why I'm aiming for a one-to-one relationship where there's not a reason for a one-to-many. Also, I get the impression that the tighter my ship in terms of data structure, the more likely I am to get my queries to work properly.
OK, so writing this down is helping. I've now realised that each name may point to only one file name and every file name points to at least one name, maybe these two tables can be linked directly by a one-to-many relationship for linking purposes (as well as the one to one relationship for the file names). On second thoughts, maybe I do need a linking table if I can't link two tables twice for two different reasons?!
The other part is about linking the styles to the file names. As this is a many-to-many relationship, there's a need for another table and I'd got the impression that this is where a composite key comes in. My logic (possibly, maybe probably flawed) says there's no need for a new primary key for this table as it's only going to be referred to in context of the 'parent' tables, the records' uniquness being defined by the combination of the two foreign keys. I'm sure I've read somewhere that using a composite key is the way forward here.
Please note that though I'm writing confidently here it's because it seems to make sense to me. I am still a newby though as despite a good few attempts over the last decade, I've yet to create a relational database that has been good enough to serve its purpose in a practical sense. I intend this to be the first so I'm trying to get it right. All suggestions, pointers to articles, disagreement with the way I'm going about it and assistance is welcome and very much appreciated.
