Hello, all --
Somehow my simple image file-organizing database has turned into
something huge and frightening. I was hoping that I could get some
input on what I have so far -- it seems correct to me but because it
links so many tables together, I wonder if I'm supposed to be doing this
much more simply?
It just seems like no one else seems to use queries as long as the ones
that my database depends on to hold together. This makes me wonder if
I'm going about this all wrong. In theory, my database seems very
relational. In practice, the queries just seem ugly and unnecessarily
big.
If you're willing to provide some insight, I have provided some data,
below. I have reconstructed the essential tables of my database, but
eliminated most of the irrelevant columns -- so what you see, while
similar in structure to what I have -- is not exactly what I'm using.
The database is called "media_db".
mysql> SHOW TABLES;
+--------------------+
| Tables_in_media_db |
+--------------------+
| files |
| projects |
| projfile |
| subprojectprint |
| subprojectweb |
+--------------------+
5 rows in set (0.00 sec)
The "files" table is used to keep track of file names and an identifying
number ("file_id").
mysql> SHOW COLUMNS FROM files;
+-----------+-----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------+-----------------------+------+-----+---------+
| file_id | mediumint(8) unsigned | | PRI | NULL |
| file_name | varchar(64) | | | |
+-----------+-----------------------+------+-----+---------+
2 rows in set (0.01 sec)
The "projects" table is used to keep track of my organization's
"projects" -- a "project" can be quite large, incorporating both web
design "subprojects" and print design "subprojects" (but "subprojects"
would never correspond to more than one "project").
mysql> SHOW COLUMNS FROM projects;
+--------------------+-----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+--------------------+-----------------------+------+-----+---------+
| project_id | mediumint(8) unsigned | | PRI | NULL |
| project_name | varchar(64) | | | |
| subprojectweb_id | mediumint(8) unsigned | | | 0 |
| subprojectprint_id | mediumint(8) unsigned | | | 0 |
+--------------------+-----------------------+------+-----+---------+
4 rows in set (0.00 sec)
Because there is a many-to-many relationship between files and projects,
I have also constructed this middle table (someone I know called it a
"foreign key" table). Etienne (from this list) taught me how to do
it -- there is a UNIQUE INDEX going in both directions on the two
columns:
mysql> SHOW COLUMNS FROM projfile;
+------------+-----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+-----------------------+------+-----+---------+
| file_id | mediumint(8) unsigned | | PRI | 0 |
| project_id | mediumint(8) unsigned | | PRI | 0 |
+------------+-----------------------+------+-----+---------+
2 rows in set (0.01 sec)
Finally, there is a bunch of data that gets stored into a row of the
"subproject" tables (which are divided into web and print,
"subprojectweb" and "subprojectprint"), but the only columns that matter
for my question are:
mysql> SHOW COLUMNS FROM subprojectweb;
+-----------------+-----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------------+-----------------------+------+-----+---------+
| subproject_id | mediumint(8) unsigned | | PRI | NULL |
| subproject_name | varchar(64) | | | |
+-----------------+-----------------------+------+-----+---------+
2 rows in set (0.01 sec)
So if I want to find out which files were used in a web subproject
called "shoeshine.com", do I really use the following query?
SELECT files.file_name
FROM files, projfile, projects, subprojectweb
WHERE files.file_id = projfile.file_id
AND projfile.project_id = projects.project_id
AND projects.subprojectweb_id = subprojectweb.subproject_id
AND subprojectweb.subproject_name = 'shoeshine.com' ;
This is a HUGE query. Well, maybe it's not -- maybe there are lots of
queries this big. The point is, I rarely see reference to a query this
big on this list or in my book. Is this the norm? Or am I doing
something weird, am I "not getting" this relational database concept?
I hope I haven't gone about this all wrong!
Thank you so much to any who can give me some advice, or confirm that
I'm doing okay.
Sincerely,
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
