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 <[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
