Hello,
I'm designing a database, and of course I have no formal training in
this area (I'm learning from DuBois's book). It's designed to keep
track of files. In my organization, we have a small design studio, and
the designers save their image files to CD-ROMs. As you can imagine,
the stacks of CDs have gotten so high that we now have invested in a
large CD-storage cabinet. Needing a way to organize these files
somehow, I was assigned the job of finding a database and setting it
up. Having always wanted to get some experience in database-driven
web-applications, I have decided to take advantage of this opportunity
and learn PHP and MySQL. Those of you who have seen me post to this
list with installation questions and have helped me, I thank you. Now
it's time to design the database.
I have come up with a few tables and am going to create them, but I
can't help but feel like my setup is flawed somehow. We don't have very
high standards for this project, but I would like it to be the best
possible and it is a learning exercise for me, so I want to use the best
possible design from the very beginning. Instead of just listing my
whole table setup and asking for input (which seems kind of
presumptious, though I wouldn't mind any advice), I'll describe my
specific situation:
I have two separate tables. One of them is called "files" and the other
is called "divisions". There are more tables, of course, but these are
the only ones that matter in this case. My organization is divided into
twelve divisions at this time, and I have assigned the name of a
division to each row in the "divisions" table, along with some other
irrelevant information like contact info, et cetera. "divisions" has an
AUTO_INCREMENT PRIMARY KEY TINYINT column as well, so that I can refer
to these entries by their identification number instead of by name. The
column is called "div_id", and can be referred to as "divisions.div_id".
In the "files" table, each row corresponds to a separate file. There's
all sorts of additional criteria, but the only one that matters to my
question is the column called "div_id". That's right, the number in
"files.div_id" must always correspond to the number in
"divisions.div_id". You might ask why I don't just use the division
name in the "files.div_id" column -- it would seem to make more sense to
have the column set up as
div_id ENUM("division1", "division2", "division3", etc...)
The reason why I am hesitant to do this is because I don't want someone
to have to use MySQL to do this:
ALTER TABLE files CHANGE div_id div_id ENUM("division1", "division2",
"division3", ... "new_division")
I would like to make this database as self-maintaining as possible. If
"files.div_id" is an INTEGER, rather than an ENUMERATION, then the user
only has to use a new number that is one number higher than the old
highest number, if we were to create add a new division to the
organization.
So does this setup seem to make sense? I mean, all I'm doing is using
an INTEGER rather than an actual name to identify a division. That
seems like it would make sense.
Here's the problem: no one wants to identify the divisions by numbers.
If they are to perform a search of files that were created by a specific
division, then they want to input the division's name, not a number. Is
there a way to use MySQL to translate the integer "divisions.div_id" to
"divisions.div_name" ? I guess what I'm asking is if this query is
possible:
SELECT files.file_name
FROM files
WHERE files.file_id = divisions.div_id
AND divisions.div_name = "division1" ;
I don't think this is a real SQL query, but I think it describes what
I'm trying to do here, I made a little diagram (this isn't output from
mysql):
+------------------------------+
| files |
+---------+-----------+--------+
| file_id | file_name | div_id |
+---------+-----------+--------+
| 32 | picture1 | 08 |
| | | |
| | | |
| | | |
| | | |
+------------------------------+
+----------------------------------------+
| divisions |
+--------+-----------+-------------------+
| div_id | div_name | contact info, etc |
+--------+-----------+-------------------+
| 08 | division1 | 123 Main St, etc |
| | | |
| | | |
| | | |
| | | |
+--------+-----------+-------------------+
I'm trying to give the name "division1" and get the name "picture1" back.
Will this work? If so, that is heartening, because I plan to have
several other tables in the database (for other criteria) which will
work in the same fashion (by assigning each entry an INTEGER rather than
a name).
Thanks,
-- Erik
"the office temp who was told to design
a database and write a web front-end,
with no experience in either"
---------------------------------------------------------------------
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
