0

After this question, I think I will go for Entity-relationship modelling to solve the issue of my image table that serves many other tables.

enter image description here

But now I have another bigger problem as there are groups/ categories in each uploaded/ inserted image, page, user, etc.

The reason I have these category tables is to allow an admin to change the category name and add more categories in whenever is need.

I use category tables to categorise each of them, for instance,

categories for image,

category_id   category_name
1             Primary image
2             Secondary image
...

for users (I duplicate the category table off image and just give the table a different name),

category_id   category_name
1             Primary user
2             Secondary user
...

And carry on duplicating the table off each other!

They looks similar inside and very redundant when I need a new category table to be added. For instance, categories for a contact list table,

category_id   category_name
1             Primary contact
2             Secondary contact
3             School
4             government
...

How can I solve this redundancy? And how the database diagram would look like?

Community
  • 1
  • 1
Run
  • 54,938
  • 169
  • 450
  • 748
  • 1
    create one category table and with a type field for user and image or both like 1,2,3 flags – Sergey Benner Feb 24 '12 at 16:04
  • Can an entity be in more than one category at a time? Do you need to categorize images too? Can categories have same name for entities of different kind (e.g. can a user **and** page be in the same category)? – Branko Dimitrijevic Feb 24 '12 at 16:04
  • `Do you need to categorize images too?` yes the images need to be categorised - Primary image, Secondary image, etc. – Run Feb 24 '12 at 16:24
  • `Can categories have same name for entities of different kind (e.g. can a user and page be in the same category)?` no, they should have their own categories - not related to each others. – Run Feb 24 '12 at 16:25
  • `Can an entity be in more than one category at a time?` no, for instant, image with `image_id` 1 only can have one category for instant - Primary image. – Run Feb 24 '12 at 16:31
  • 2
    I think a different category_table per table that needs categories is fine. The data only seem duplicate. If you skip the first 2 rows ("First something", "Second something"), they look pretty different to me. – ypercubeᵀᴹ Feb 25 '12 at 16:08

1 Answers1

0

The names of categories for pictures are generally different from the names of categories for users, and both those are different from the names of categories for pages. That suggests the values are drawn from different domains. Different domains mean different tables.

Create one table for each kind of category. Use foreign keys.

Redundant is a technical term in database design. It doesn't mean "these two tables look a lot alike". It means the tables have the same values, and those values have the same meaning.

It's clear that "Primary image" doesn't have the same value as "Primary user". But let's say, for the sake of argument, that both those tables had the row (1, Primary). That still not redundant, because those two values have different meanings. In the one case, it means that whatever image is tagged with it is a primary image. In the other case, it means that whatever user is tagged with it is a primary user. Images are not users. Different meanings.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185