I have a schema with a few tables 1 parent and 3 children.
The primary key of the parent table is the foreign key in the child tables and that foreign key is each child tables primary key.
I have been using MySQLWorkbench to do my entity relation diagrams.
It has a handy feature where it creates sql scripts for you.
It generates the create statements and is also supposed to create insert statements as well.
(For some reason the insert statements aren’t being generated but that’s a different story)
The create statements have ON DELETE and ON UPDATE NO ACTION.
I think the DELETE should CASCADE, but I’m not sure what the update button should do…
The child tables are just to hold a Xojo Dictionary.
Anyone ever dump a dictionary to a SQL table for use as an SQL table?
I’m not sure why the third child has a solid line vs the dashed line … Something to do with identifying the key ??
First of all, the FK column should not be the PK of the child tables. This will limit you to one child per parent. If you really only need one child, then why use a separate table? The data should reside in the primary table.
As to the ON DELETE action, it depends on how you wish it to function. The NO ACTION option prevents anyone from deleting a parent without first deleting all children (from your app, someone else’s app, a db manager, etc.). The CASCADE option performs these deletes automatically - in other words it’s a lot easier to wipe out an entire family in one command (parent and all children). Leaving it at NO ACTION can save your behind if you thought you were deleting a childless record, but picked/typed the wrong one.
Same applies to ON UPDATE action, except it’s really only useful if your key value is something meaningful - not just an auto-incrementing id. Something like a product code that may be changed later.
How do you store a list of (key, value) into a column of a row?[/quote]
Your first image came through in the email notification - not sure why it doesn’t show here.
As to storing key/value pairs, the structure shown in your image is almost correct. You just need to add an autoinc PK to each child table. Then you insert one row (with the parent id) for each key/value pair.
One other note - why are you using a Blob for the value. Are they images or other binary data?
I usually only store strings, but those strings can be a message in mandarin or latin or greek or a very small image.
The parent can have 0 to 1 of each of the three types of children. (I know there are three tables but they each have the same parent and cardinality.)
