Hi,
I am following up on a thread I started yesterday about building my
tables. (I have included the original email at the end of this one for
reference.) I got some good feedback about it, and so have decided to
change my entire battle plan, and was hoping to get some advice about it.
The scenario is this: a table exists called "properfiles", and its
columns keep track of various attributes of files. Each record is a
file, of course. These files have been "properly" entered into the
database using a PHP application I am writing, and thumbnails have been
uploaded to the filesystem for each of these records.
A user can use the application to select any of these files to be
printed, adding the files to a shopping-cart-like object. But in
addition to printing these already-existing files, the user needs to be
able to define their own files to be printed -- these don't already
exist in the database. However, I would like to keep track of them,
since they are being printed.
So here's the dilemma -- I've got some "proper" files that have been
entered "properly" into the database, and have a great deal of
information stored about them, and I have some "user-entered" files that
have been entered only for the sake of being printed. The attributes of
a "user-entered" file are different from the attributes of a "proper"
file.
Should I have one big table named "files" which keeps track of both
kinds of files? This seems dodgy to me -- that would mean that some
columns would be for "proper files" (meaning that a "user-entered" file
record would have a null value for that column) and some columns would
be for "user-entered" files (so "proper" files would have a null value
for that column). Is this an acceptable database design?
Originally I was thinking of storing each in its own table --
"properfiles" and "userfiles", and having a "prints" table act as a
foreign key, storing one-to-many relationships between "properfiles" and
"userfiles" (for each "print" there could be any combination of
"properfiles" and "userfiles"). But this would require having some way
of tracking whether the file_id matches a "proper" file_id or a "user"
file_id. It doesn't seem very smart to have a setup like this.
So I think I should go with the big "files" table and just have some
columns that apply to some kinds of files and not to others. Is this
done? I'm just hesitant to have a lot of null values in my table. What
do you think?
Thanks for reading! Thanks even more if you can help me.
Erik
On Thursday, April 18, 2002, at 03:52 PM, Erik Price wrote:
> 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
