asked
Identity as the Foreign key with multiple tables.
I have 4 tables in all ..3 for data and 1 for storing the Images.People can insert information in the 3 tables and the images uploaded will be stored in the image table keeping the Identity key from the data tables as the Key Id int he Image table. Now how do i prevent the image table from not having a replication data..Like if the identity has 2 in one table it could be that 2 could be there in another table..But in the image table the foreign key which mean that its not necessary it will bet me the correct record from the Image table .
Ok, so you have: * Table Images, with primary key ImageID, integer * Datatables Table1, Table2 and Table3, with primary keys Table1ID, Table2ID and Table3ID, all integer identity-columns. * ImageID is related to Table1, Table2 OR Table3. * The relationship is between Images and the datatables are (0 or 1) to 1. You have already spotted one flaw in your database design - the primary key of tables Images is not necessarily unique. Another flaw is that you'll have a hard time enforcing referential integrity. I think I'd reverse the relation, in a way that I'd choose an identity column as primary key for the Images table, and put ImagesID as foreign key column in the three data-tables. Set the ImageID-column in the data-tables to NULL as default, and when an Image is uploaded, you update the ImageID of the data-table in question. Another way would be to, again, use identity-column as primary key in Images-table, and create three foreign key-columns in the Images-table, Table1ID, Table2ID and Table3ID, and only put a value in the appropriate column (depending on the source data-table). But I feel that neither of these designs are very good, and I'm also not sure I've understood your requirements, or why you have chosen the database design you have chosen. Please share some more background information and your purpose of having the design you have.
In that case, I'd use integer identity column as primary key in Images-table and add ImagesID as a foreign key column in the three data tables.
Your ideas are brilliant Mr.Greg and Mr.Magnus Ahlkvist ..I cant appreciate you enough for you time...Actually i am storing sme images and don't want it to have multiple tables etc..I have like a movie table ,Like a sport table like a general table ..But the place t store images is same and i dont want to have different tables for Images just to avoid confusion later..and if I add another table (for eg.Books ) i need another table with same structure for ImageBooks table..hence this question arose in the first place...
If I understand your question I think you would be better off making three IMAGES tables, one for each of the data tables. This way you can use foreign keys that enforce referential integrity. To have one IMAGES table would require more programming. Alternatively you could add a column to identify the table the image is related to but this just adds unnecessary overhead and doesn't leverage the foreign key mechanisms.
9 People are following this question.
