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:
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.
answered Aug 13, 2012 at 02:00 PM
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.
answered Aug 13, 2012 at 12:26 PM