Hi!
I have a question related to database (MySQL) design.(sorry for my poor english)
Please first take look a this pictures.
[IMG]http://i43.tinypic.com/ao3yr.jpg[/IMG]
[IMG]http://i40.tinypic.com/102l3s3.jpg[/IMG]
I have a table "Device" with Name, Dll and idDevice(PrimaryKey) as attributes. I have ForeignKey relationship (n:1) with other tables(ATBLOG, ATBVVLOG, DIGIO...,).
I have inserted Primary key of the table "Device" to each of the other tables as foreign key. It is a kind of Generalization.
Now I have two problems and some questions.
Problem 1 If I want to insert data into one of the tables (Device, or the childtables like ATBLOG, ATBVVLOG...) i get a ForeignKey-constraint error. That´s because I have to enter a value for the foreign keys in my table "Device" or in my table ATBLOG for example. That´s a kind of chicken-and-egg-problem.
The only solution I have found is to set SET FOREIGN_KEY_CHECKS = 0; and it works.
Problem 2 Only one Device (ATBLOG, ATBVVLOG..) should be related to the "Device" table at one time.
So all other values of the foreignkey (in "Device")should have NULL, but I get "0" for each of the other tables as ForeignKey values automatically.
Questions:
Is there a way to redesign my solution?
Is there a way that ensures that you can only enter one value for a device(ATBLOG...) as foreignKey in the Device table? Some kind of SQL Constraint?
Can you see other problems with my tables?
regards
Adam