One to Many Design

[paul.m.henderson]

Hi,

I've got a database that I'm in the middle of redesigning and came
across an interesting situation. The basic purpose of the database is
to catalog objects within a room (think facilities management). A
major requirement is that each object must have multiple photographs.
Now, the photographs will not be stored in the database, but rather
only the file path. However, the problem is that there are multiple
items that can have photographs. These items range from objects in a
room, to employees, to the room itself to the structure housing the
room. So, what I've got is one photographs table with an object type
field which is used to distinguish what the photograph is of, and a
foreign key. I've included some sample records below:

photographId | photographType | foreignKeyId
1 | structure | 1
2 | room | 1
3 | object | 1

I know that proper database design would have a separate table for each
type of photograph, however, that seems redundant as each table would
have the same fields and hold basically the same types of information.
Is there a better way to design for this situation? Thanks!

 

[Tom Lake]

I know that proper database design would have a separate table for each

type of photograph, however, that seems redundant as each table would
have the same fields and hold basically the same types of information.

I would consider a good design having that additional field that identified
the type of photograph. Just the way you described.

Tom Lake

 

[paul.m.henderson]

The only thing I don't like about the current design is that there
can't be a relationships built using the photographs table. Any ideas
on how I can implement cascading updates/deletes using the current
design?

 

[mnature]

I've got a database that I'm in the middle of redesigning and came

across an interesting situation. The basic purpose of the database is
to catalog objects within a room (think facilities management). A
major requirement is that each object must have multiple photographs.
Now, the photographs will not be stored in the database, but rather
only the file path. However, the problem is that there are multiple
items that can have photographs. These items range from objects in a
room, to employees, to the room itself to the structure housing the
room. So, what I've got is one photographs table with an object type
field which is used to distinguish what the photograph is of, and a
foreign key. I've included some sample records below:

photographId | photographType | foreignKeyId
1 | structure | 1
2 | room | 1
3 | object | 1

I know that proper database design would have a separate table for each
type of photograph, however, that seems redundant as each table would
have the same fields and hold basically the same types of information.
Is there a better way to design for this situation? Thanks!

You have photographs. All photograph links should go into one table. This
is pretty straightforward.

tbl_Photographs
PhotographID (PK)
PhotographLink (this would be the url to the actual photograph)

Then you have different types of photographs, such as objects, employees,
rooms, structures, etc. You could just dump these different types into
another table, and set the primary key of that table as a foreign key in the
Photographs table. However, there is probably a common link between these
different types, which could be the structure that everything is in, or it
could be the company that is hiring you to take the photographs, or it could
even just be what time of year it is. But I would suppose that this common
link should be what ties your actual photographs, and what type of
photographs they are, together.

Let us assume that you are hired by a company to take photographs.

tbl_Companies
CompanyID (PK)
CompanyInfo

tbl_PhotographTypes
PhotographTypeID (PK)
PhotographTypeInfo

tbl_Photographs
PhotographID (PK)
PhotographURL
PhotographInfo

tbl_CompanyPhotographs
CompanyPhotographID (PK)
CompanyID (FK)
PhotographTypeID (FK)
PhotographID (FK)
CompanyPhotographInfo

Info fields are for whatever miscellaneous information may be required in
the specific tables. This format would tie together the company requesting
the photographs, what type of photograph it is, and where to find the
photograph. You could put the PhotographTypeID in the Photographs table.
Not sure which way would be best for your application.