Hello,
I was hoping to solicit some advice on the structure of the database I
am building. Here is a simplified version of my scenario:
The application that I am designing, in PHP4.1.2 with MySQL (3.23.46 on
RedHat 7.2), has a feature which allows it to keep track of graphics
files (JPGs, GIFs, PSDs, AIs, QPTs, etc), which are stored on CDs in a
CD cabinet. I have already designed a table to store information about
files, where each row is an individual file and each column is an
attribute of that file.
mysql> describe files;
+-------------+-----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------+-----------------------+------+-----+---------+
| file_id | int(10) unsigned | | PRI | NULL |
| file_name | varchar(64) | | | |
| filetype_id | smallint(5) unsigned | YES | | NULL |
| stor_id | mediumint(8) unsigned | YES | | NULL |
| file_size | float(4,2) | | | 0.00 |
| width_in | float(4,2) | | | 0.00 |
| height_in | float(4,2) | | | 0.00 |
| file_res | smallint(5) unsigned | YES | | NULL |
| cre_date | date | YES | | NULL |
| insert_date | datetime | YES | | NULL |
| inserter_id | smallint(5) unsigned | YES | | NULL |
+-------------+-----------------------+------+-----+---------+
(there are other tables that relate to this one, but they are not
relevant to my problem)
Using my application via a web browser, users can select existing file
records which need to be printed, and store them as objects into an
array (actually a session variable array), similar to a shopping cart.
Later, they will use another section of the application to "check out",
meaning each object in the array will become a row in another table
("printedfiles"), representing files that have been selected to be
printed. (What happens with this data is irrelevant to my problem)
The problem comes into play here: in addition to files they have
selected from the database (let's use the alias "files" since they are
all rows from the "files" table), users may also select files which do
not exist in the database yet -- let's use the alias "user files".
I do not wish to store "user files" in the "files" table, because they
are different entities. But I would like to have these entities stored
in the "printedfiles" table, along with any "files" that were selected
using the application. What is the best way to do this? Originally, I
was thinking that I could have a printedfiles.file_id column, which
would store either the file_id of a "files" record, or the file_id of a
"userfiles" record, depending on whether or not the printedfiles record
was selected using the application or defined by the user.
But then I wondered if that was a good idea after all -- after all, how
would I know whether the printedfiles.file_id was a "files" file_id or a
"userfiles" file_id ? To solve this, I thought I could add a column like
ALTER TABLE printedfiles ADD COLUMN source ENUM('files', 'userfiles')
to qualify the file_id as belonging to either a "files" record or a
"userfiles" record. But I am wondering if this is really a good idea --
when I need to query for all records in "printedfiles" and categorize
them, will I be able to use this "source" column? Or is there a better
way I could be going about all of this...
Thanks for any insight you can provide.
Erik
----
Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
