How I nomalize my this database design?

[thara]

I have two mysql tables named "management" and "services".

 

"management" table have fields:

- id

- name

- designation

- description

- added_date

 

"services" table have fields:

- id

- name

- description

- added_date

 

Every persons in "management" table should have one image. (their profile image) and in "services" table it will have multiple services and each service should have multiple images.

 

So my question is, How I get these images to one table named "image_info" and how they separate each other?

 

Hope somebody may help me out.

 

Thank you.

 

[thara]

I tried it using two tables like this : 

CREATE TABLE IF NOT EXISTS image_category(
    category_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(60) NOT NULL,  
    PRIMARY KEY (category_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO image_category
                        (name)
                        VALUES
                        ('service'),
                        ('management');


CREATE TABLE IF NOT EXISTS image_info(
    image_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT(4) NOT NULL,
    image VARCHAR(80) NOT NULL,
    image_path VARCHAR(150) NOT NULL,
    extention VARCHAR(10) NOT NULL,
    image_size VARCHAR(10) NOT NULL,
    dimension VARCHAR(15) NOT NULL,
    mime_type VARCHAR(20) NOT NULL,
    alt_text TEXT DEFAULT NULL,
    added_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (image_id),
    UNIQUE (image)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

But not sure how it related profile images and service images?

 

Do I not need to store profile images in separate table like this?

 

Why I trying do is, I need to get all my images into one table..

 

Any idea would be greatly appreciated.

 

Thank you.

[CroNiX]

Add a column to managers table to store image name, since each manager will ONLY have one image per manager. Perhaps "image_name".

 

Since services can have more than one image per service, create a separate table for "service_images".

-id

-service_id (fk -> services.id)

-image_name

-image_order (if you want to order them in a certain way, like for displaying)

[Barand]

Management has a singlw image so that can go in the management table

 

Services have many images so you need a table to store the images for each service, 1 image per row linked by the service id.

+---------------+
| management    |
+---------------+
| id            |
| name          |
| designation   |
| description   |
| added_date    |
| profile_img   |
+---------------+
 
+---------------+         +-----------------+
| services      |         | service_image   |
+---------------+         +-----------------+
| id            |----+    | serv_img_id     |
| name          |    +---<| service_id      |
| description   |         | service_image   |
| added_date    |         +-----------------+
+---------------+

[CroNiX]

@barand do you manually create those ascii chart/tables for db schemas or do you have some tool that does it?

[Barand]

I do it the laborious way. I have often thought of creating a tool (in fact every time I create a new diagram)

[Zane]

@barand do you manually create those ascii chart/tables for db schemas or do you have some tool that does it?

 

I always wondered the same thing

 

I do it the laborious way. I have often thought of creating a tool (in fact every time I create a new diagram)

 

Damn, that's a lot of work.  You must just be so used to it that you write it without thinking though.

[Barand]

When I first bought myself a Mac computer back in 1984/5 one of the first things I did was to get a BCPL compiler (there wasn't much else available in those early Mac days) and write an application to draw data flow diagrams, entity relation diagrams and entity life histories, all will draggable objects and smart connectors. Made my job as systems designer easier. So I suppose something to do the ascii diagrams should be easy, but if I have to define the db tables first I may as well use Workbench to produce a model.