x

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 .
more ▼

asked Aug 13, 2012 at 09:36 AM in Default

fashraf gravatar image

fashraf
418 13 14 20

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Aug 13, 2012 at 02:00 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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...
Aug 14, 2012 at 12:04 AM fashraf
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.
Aug 14, 2012 at 05:18 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 13, 2012 at 12:26 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x35
x34

asked: Aug 13, 2012 at 09:36 AM

Seen: 1295 times

Last Updated: Aug 14, 2012 at 07:46 AM