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/[EMAIL PROTECTED]
