I understand now. There needs to be a table in between "files" and "types". This allows me to assign two different rows in the "types" table (say, JPG and JPEG) to the same row in the "files" table, or two different rows in the "files" table to the same row in the "types" table.
This seems like quite an extra step. I am prepared to do it if that is what must be done -- let me guess: it's not that hard if you are careful to construct SELECT, INSERT, and UPDATE statements that JOIN the three tables together in the proper combination! Okay, I will just have to be very careful about that (this project was much easier when the relationships were all one-to-one !! :-) . Thank you very much for your help, Etienne. -- Erik On Tuesday, December 4, 2001, at 01:23 PM, Etienne Marcotte wrote: > hum I have an hard time understanding, but if I'm right: > > CREATE TABLE files( > fileID smallint unsigned auto_increment, > filename varchar(36) not null, > primary key (fileID) > ) > > CREATE TABLE types( > typeID smallint unsigned auto_increment, > typename varchar(36) not null, > typeext char(4) not null unique, > primary key (typeID) > ) > > You'll need a third table linking the two (because it will be a N:N > relationship) A file may have many extensions and an extension may have > many files. > > CREATE TABLE filetypes ( > fileID smallint unsigned not null, > typeID smallint unsigned not null, > unique index (fileID,typeID), > unique index (typeID,fileID) > ) > > Now insert some dummies > > mysql> select * from files; > +--------+----------+ > | fileID | filename | > +--------+----------+ > | 1 | foo | > | 2 | bar | > | 3 | baz | > +--------+----------+ > 3 rows in set (0.00 sec) > > mysql> select * from types; > +--------+-----------------+ > | typeID | typename | > +--------+-----------------+ > | 1 | photoshop image | > | 2 | word document | > | 3 | excel sheet | > | 4 | jpeg image | > | 5 | jpeg image | > +--------+-----------------+ > 5 rows in set (0.00 sec) > > Now let's say you have an image that can have either jpeg or jpg: > > mysql> select * from filetypes; > +--------+--------+ > | fileID | typeID | > +--------+--------+ > | 3 | 1 | > | 1 | 2 | > | 2 | 4 | > | 2 | 5 | > +--------+--------+ > 5 rows in set (0.00 sec) > > mysql> SELECT filename, typename, typeext FROM files, types, filetypes > WHERE filetypes.fileID = files.fileID AND filetypes.typeID = > types.typeID AND filename LIKE "bar"; > +----------+------------+---------+ > | filename | typename | typeext | > +----------+------------+---------+ > | bar | jpeg image | jpg | > | bar | jpeg image | jpeg | > +----------+------------+---------+ > 5 rows in set (0.00 sec) > > I hope it's what you wanted > > Etienne > > btw, if you find any mailing list ont he web for general relational DB > design issues, let me know. I searched and could not find any:( > > Erik Price wrote: >> >> Hello, >> >> I was looking for some advice on building my database. If this is an >> offtopic question, I apologize in advance! >> >> I'm building a database with several tables. Only two of them pertain >> to my question. Also, as I have not yet built my tables (I'm planning >> them), I can't include contents of a dump. >> >> One of the tables is called "files", the other is called "types". Here >> is a quick sketch of what "files" looks like (there is more but this is >> really all that matters): >> >> +---------+-----------+---------+ >> | file_id | file_name | type_id | >> +---------+-----------+---------+ >> | | | | >> | | | | >> | | | | >> | | | | >> +---------+-----------+---------+ >> >> here is "types": >> >> +---------+-----------+-----+ >> | type_id | type_name | ext | >> +---------+-----------+-----+ >> | | | | >> | | | | >> | | | | >> | | | | >> +---------+-----------+-----+ >> >> You can probably figure out what I'm doing here. file_id and type_id >> are INTEGER-based primary keys which simply give me a nice reference >> number to give each row. file_name and type_name are VARCHAR(36) >> columns. files.type_id is really the same as types.type_id, and >> types.ext is a VARCHAR(5) column. Queries will look like this: >> >> SELECT files.file_name >> FROM files, types >> WHERE types.ext LIKE "txt" >> AND files.type_id = types.type_id ; >> >> So that a user can enter "txt" as a file's extension and all the files >> that are .txt files will be returned. >> >> First of all, I hope I'm doing this right. >> >> Second of all -- some files types (file formats) have more than one >> extension. For instance, I write HTML files and use JPEGs. But >> sometimes I'll use a graphics program that automatically renames the >> file ".JPG" and I won't change it because it's too much of a pain. Or >> someone I work with might have use Windows, and instead of writing a >> .html file, they may have their extension as .htm (the "l" is missing). >> >> What is the best way to accommodate all of this? I would like to make >> the "types" table a comprehensive list of all file formats with their >> associate extensions so that when a filename comes up, the user can >> easily see what format that file is in (and there would be other >> columns, such as "open_in" with the name of an application to open that >> file with; e.g.: Photoshop for ".psd" or Illustrator for ".ai"). >> >> Any advice? >> >> Thank you, >> >> Erik Price >> >> --------------------------------------------------------------------- >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail <mysql-unsubscribe- >> [EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- > Etienne Marcotte > Specifications Management - Quality Control > Imperial Tobacco Ltd. - Montreal (Qc) Canada > 514.932.6161 x.4001 > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
