Re: more than one possible column value - Etienne Marcotte - com.mysql.lists.mysql

5 messages in com.mysql.lists.mysqlRe: more than one possible column value

From	Sent On	Attachments
Erik Price	04 Dec 2001 09:37
Etienne Marcotte	04 Dec 2001 10:23
Erik Price	04 Dec 2001 10:45
Erik Price	04 Dec 2001 11:36
Etienne Marcotte	04 Dec 2001 11:43

Subject:	Re: more than one possible column value
From:	Etienne Marcotte (emar...@itl.ca)
Date:	12/04/2001 10:23:25 AM
List:	com.mysql.lists.mysql

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)

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)

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,

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-emarcott=itl...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php