Posted by Jerry Stuckle on 11/25/05 04:23

Henri wrote:
> Hello,
>
> I'm learning sql right now and decided, in this very purpose, to design
> a database for all my images; a database which I will maintain and
> upadate with php.
> Now. At first I figured I'd create one table with the following
>
> image:
> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> category VARCHAR(32) NOT NULL default '',
> title VARCHAR(32) NOT NULL default '',
> year YEAR NOT NULL,
> description TEXT,
> keywords VARCHAR(64),
> filename VARCHAR(32) NOT NULL default '',
> mime VARCHAR(12) NOT NULL default '',
> size INT NOT NULL,
> height INT NOT NULL,
> width INT NOT NULL,
> image BLOB NOT NULL
>
> but then I figured, I dunno, why not 2 tables? Because the kind of
> information related to each image is of 2 different kinds
>
> One is about classification:
>
> image_data:
> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> category VARCHAR(32) NOT NULL default '',
> title VARCHAR(32) NOT NULL default '',
> year YEAR NOT NULL,
> description TEXT,
> keywords VARCHAR(64)
>
> and the other about the physical specifications as well as the binary data:
>
> image_specs:
> id INT NOT NULL PRIMARY KEY,
> filename VARCHAR(32) NOT NULL default '',
> mime VARCHAR(12) NOT NULL default '',
> size INT NOT NULL,
> height INT NOT NULL,
> width INT NOT NULL,
> image BLOB NOT NULL
>
> Plus, I suspect most queries will be done on the first table.
>
> Does that make any sense? More to the point: does that actually help
> database management?
>
> Thanks in advance for your input.
>
> Henri
>
Why have two tables? It looks like both tables are dependent on the
same ID.

Even though the data is of two different kinds, there is still a 1:1
relationship between the data item and the primary key. And if you just
select the columns you need, you should see little additional overhead.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]