Single or Multiple Tables?

#1

Hi

I was wondering which of the design approaches below would be better:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

Any help would be greatly appreciated.

Thanks

#2

On Aug 12, 3:46 am, Ian Oxley <ijox... (AT) googlemail (DOT) com> wrote:

Quote:

Hi

I was wondering which of the design approaches below would be better:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

Any help would be greatly appreciated.

Thanks

From the information you gave, there is no clear answer.

A DATABASE is a model of the external world. So the question is: what
are you modeling?

One way to get at the answer is to ask how you would query the
database. Does you plan to search for resources or would you be
searching for images, documents, etc. What would your WHERE clause
look like? You gave no other attributes for these "resources".

Or are you just using the database as an expensive file system?

Ed

#3

On Aug 12, 3:46 am, Ian Oxley <ijox... (AT) googlemail (DOT) com> wrote:

Quote:

Hi

I was wondering which of the design approaches below would be better:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

Any help would be greatly appreciated.

Thanks

From the information you gave, there is no clear answer.

A DATABASE is a model of the external world. So the question is: what
are you modeling?

One way to get at the answer is to ask how you would query the
database. Does you plan to search for resources or would you be
searching for images, documents, etc. What would your WHERE clause
look like? You gave no other attributes for these "resources".

Or are you just using the database as an expensive file system?

Ed

#4

On Aug 12, 3:46 am, Ian Oxley <ijox... (AT) googlemail (DOT) com> wrote:

Quote:

Hi

I was wondering which of the design approaches below would be better:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

Any help would be greatly appreciated.

Thanks

From the information you gave, there is no clear answer.

A DATABASE is a model of the external world. So the question is: what
are you modeling?

One way to get at the answer is to ask how you would query the
database. Does you plan to search for resources or would you be
searching for images, documents, etc. What would your WHERE clause
look like? You gave no other attributes for these "resources".

Or are you just using the database as an expensive file system?

Ed

#5

On 12.08.2008 14:03, Ed Prochak wrote:

Quote:

On Aug 12, 3:46 am, Ian Oxley <ijox... (AT) googlemail (DOT) com> wrote:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

From the information you gave, there is no clear answer.

That's true. It sounds as if all these "resources" are treated
uniformly. In that case I'd probably have a single table with a BLOB
for the data and a column for storing the MIME type - along with other
information necessary to identify resources.

Quote:

Or are you just using the database as an expensive file system?

Expensive but transaction safe and fully integrated in backup etc.!

Kind regards

robert

#6

On 12.08.2008 14:03, Ed Prochak wrote:

Quote:

On Aug 12, 3:46 am, Ian Oxley <ijox... (AT) googlemail (DOT) com> wrote:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

From the information you gave, there is no clear answer.

That's true. It sounds as if all these "resources" are treated
uniformly. In that case I'd probably have a single table with a BLOB
for the data and a column for storing the MIME type - along with other
information necessary to identify resources.

Quote:

Or are you just using the database as an expensive file system?

Expensive but transaction safe and fully integrated in backup etc.!

Kind regards

robert

#7

On 12.08.2008 14:03, Ed Prochak wrote:

Quote:

On Aug 12, 3:46 am, Ian Oxley <ijox... (AT) googlemail (DOT) com> wrote:

Within the admin section of a website administrators can upload
Resources for inclusion throughout the site. Now, these Resources can
be either images, videos or documents e.g. PDF's, etc.

Which of the following solutions would be best?

1. Creating a single table named Resources into which all resources
would be inserted, then having some way of differentiating between the
different types of resources e.g. a 'resource_type' column; or

2. Creating separate tables for each type of resource e.g. Images,
Videos, Documents and inserting each resource into the appropriate
table.

From the information you gave, there is no clear answer.

That's true. It sounds as if all these "resources" are treated
uniformly. In that case I'd probably have a single table with a BLOB
for the data and a column for storing the MIME type - along with other
information necessary to identify resources.

Quote:

Or are you just using the database as an expensive file system?

Expensive but transaction safe and fully integrated in backup etc.!

Kind regards

robert

#8

From the spec you gave, I would tend to a single table for Resources
qua Resources with dates, sources, descriptions, media type, etc, that
includes a column for the names of the files that contain the actual
images, videos or documents. Then lock or encrypt the files so that
only the Admin can get to them to prevent tampering.

I never liked putting BLOB style data into SQL. It gets too
proprietary, you cannot query it anyway and next week someone invents
a new spiffy media that you cannot handle properly.

#9

From the spec you gave, I would tend to a single table for Resources
qua Resources with dates, sources, descriptions, media type, etc, that
includes a column for the names of the files that contain the actual
images, videos or documents. Then lock or encrypt the files so that
only the Admin can get to them to prevent tampering.

I never liked putting BLOB style data into SQL. It gets too
proprietary, you cannot query it anyway and next week someone invents
a new spiffy media that you cannot handle properly.

#10

From the spec you gave, I would tend to a single table for Resources
qua Resources with dates, sources, descriptions, media type, etc, that
includes a column for the names of the files that contain the actual
images, videos or documents. Then lock or encrypt the files so that
only the Admin can get to them to prevent tampering.

I never liked putting BLOB style data into SQL. It gets too
proprietary, you cannot query it anyway and next week someone invents
a new spiffy media that you cannot handle properly.