[s.selland]

Hi

I am in the process of designing a database for a simple Content Management System (CMS) and I've run in to some problems.

First of all, the purpose of the CMS is to keep track of a set of different articles whose content and layout are different from each other. For example there are news articles and articles describing the employees of a company.
A news article will have a heading, a text, and two pictures ( It can not have more than two pictures), while the employee description will have employee name, personal statement and one picture.
There are several other predefined layouts but these two will do for illustrating my problem.

The tables (somewhat amputated) for articles and employees are:

Articles(articleID, name, description, pic1ID, pic2ID, text)
Employees(EmployeeID, firstname, lastname, picID, statement)

My problem is to keep track of pictures on the web server.
Before a picture can be used in an article, it must be uploaded to the server and registered (happens automatically) in the database. The table for uploaded pictures is:

Pictures(pictID , picname, alttag, picdescrib, picURL)

A picture may be used by many articles, employee descriptions etc, so we have M:N relationship between the picture table and all of its users (eg articles and employees).

I do not want the web server to be overloaded with pictures from articles that have been deleted, thus I would like to be able to list all pictures that are currently not used by any articles/employee descriptions.
So I guess I need a table that records the relationship between pictures and articles/employee description, but I cann't come up with a proper solution.


Any help or clues to a solution to this problem will be higly apreciated

Cheers

Stian Selland

[Jonte]

I think you got all the information you need in your db.

Following code lists all id´s from the picture table that don´t have an corresponding id in the Employees table.

select p.picid from pictures p
left join Employees e on (p.picid = e.picid)
where e.picid is null

the query can be modified to return all the information you need.

Hope it helps!