Jump to content

How I nomalize my this database design?


thara

Recommended Posts

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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    |         +-----------------+
+---------------+
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.