Hi folks,
how would you design a 1:1 relation ?
I'd like to split an entities's attributes because they won't get
equally frequently requested. So I can save memory and disk access time.
The integrity of the splitted record should be retained.
The key points would be:
1) devide the data collumns on n tables
2) enforce the existence of all parts of a record by table design
The best workable way I can think with only two tables is 1:(0,1)
which is not exactly the deal. It provides that table_b can hold 0 or 1
part relating to a part in table_a, by a foreign key in table_b and an
UNIQUE.
I tried another way with
1) creating table_a
2) creating table_b with FK to table_a
3) alter table_a with a FK to table_b
Now that was a 1:1 relation.
Obviously I couldn't insert anymore. A insert in table_a would violate
the existence integrity constraint to table_b.
SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS = 0;
insert table_a ...
insert table_b ...
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
would let me insert but the inserted data wouldn't be checked at all.
That way I could forget using this relation constraint alltogether.
Are real 1:1 relations mythical beeings that live only between the dusty
covers of db-design textbooks or - more likely - am I just ignorant ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
