Geek asks and answers

Is normalization required in this case?

Say I have a database with the following table:

id | fundraiser_id | donation_amount | name | sex | university

This is a similar version of my real table. This table tracks donations during fundraising. It is very likely that the same person will donate several times to each fundraiser (they are very generous).

The user enters this data as a spreadsheet. They will not necessarily make sure that they enter the same name, gender and university for several lines. In the end, they do not pay for storage space.

My question is this: Do I have to normalize this table, trying to update the table to extract the individual values for the set name, sex, universityand save them in the table People. Then my new table will look like this:

id | fundraiser_id | donation_amount | people_id

If the user predetermines the people they will use in their table, this is not an option.

I discussed this situation in the previous question , but I felt that I did not give enough details. Here are the pros and cons that I (and others who helped me in this previous question) came up with:

Pros:

  • Less wasted space
  • More "normal"
  • Faster to answer some inquiries

Minuses:

  • Accepts resources to ensure that I do not add duplicates to the People table.
  • Cron, People.
  • , , .

.

:

, , . , - . , .

+1
5

. , "id" .

Table: donations
id   fundraiser_id   donation_amount   name              sex   university
--
1    100             $100              Kim Stack         M     Rivier College
2    100             $150              Kim Stack         M     Rivier College
3    100             $45               Marguerite Meade  F     Rivier College
4    100             $100              Marie Dew         F     Rivier College
5    100             $100              Kim Stack         F     Midway College
6    100             $100              Kim Stack         F     Mars Hill College
...
98   200             $135              Kim Stack         M     Rivier College
99   200             $400              Kim Stack         M     Midway College

, .

  • name- > sex: , " ".
  • name- > : .
  • , - > : , " " .
  • , - > : , .
  • , - > : , - "-" .

( .)

, "id" - -: 5NF.

, id {name, sex, university} id - .

+3

, :

id | some_unique_field | name | sex | university

:

id | fundraiser_id | donation_amount | name | sex | university

:

donation
id | fundraiser_id | donation_amount | donator_id

fundraiser
id | charity | ....

donator
id | name | sex | university

, .

, - :

1: :

DROP TABLE IF EXISTS `test`.`bh_donations`;
CREATE TABLE  `test`.`bh_donations` (
  `fundraiser_name` varchar(45) NOT NULL,
  `donation_amount` decimal(10,2) NOT NULL,
  `name` varchar(45) NOT NULL,
  `sex` char(1) NOT NULL,
  `university` varchar(45) NOT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1;

id, , .

2 , .

DELIMITER $$

CREATE TRIGGER bi_bh_donations BEFORE INSERT ON bh_donations FOR EACH ROW
BEGIN
  DECLARE mydonater_id integer;
  DECLARE myfundraiser_id integer;

  SELECT f.id INTO myfundraiser_id FROM fundraiser f 
    WHERE f.name = new.fundraiser_name LIMIT 1;

  IF f.id IS NULL THEN BEGIN
    SELECT error_fundraiser_is_unknown FROM table_error;
  END; END IF;

  SELECT d.id INTO mydonator_id FROM donator d
    WHERE d.name = new.name AND d.sex = new.sex AND d.university = new.university
  LIMIT 1;

  IF mydonator_id IS NULL THEN BEGIN 
    INSERT INTO donator (name, sex, university)
    VALUES (new.name, new.sex, new,university);
  END; END IF;

  SELECT LAST_INSERT_ID() INTO mydonator_id;

  INSERT INTO donation (fundraiser_id, donation_amount, donator_id)
    VALUES (myfundraiser_id, new.amount, mydonater_id); 
END$$

DELIMITER ;

3 LOAD DATA INFILE

LOAD DATA INFILE 'data.csv' INTO TABLE bh_donations
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

excel blackhole, Excel CSV.
LOAD DATA INFILE, .

: : http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html
:
: http://dev.mysql.com/doc/refman/5.5/en/triggers.html
load data infile: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

, .

+5

:

id some_unique_field; , .

, , (, ). id. , (, , , - , - , , ), .

"" " " - .

+3

. , , - , . , , , , , , . , MySQL ( atm), INSERT IGNORE, .

... TRIGGER, , "" .

+1

You can create an UNIQUE index (name, gender, university). This will prevent two entries from entering the database.

However, there is a separate issue that may bother you: identify spelling options (for example, "My W." and "My University") versus "My University").
+1
source

More articles:


All Articles