Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hdv212
Posting Yak Master
140 Posts |
 Posted - 2009-05-07 : 07:17:55
|
| Hii have some tables in my database like this :Table1Table2Table3each record of each table can have one or more images, hence i need a Table to hold only images and parentId field, but my problem is that how can i create a one table to hold images of all tables and have relationShips with other tables ?what's your idea ?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-07 : 07:35:13
|
| You can't enforce it with a foreign key, you'd have to use a trigger to query the tables for that ID, and rollback if it doesn't exist in any of them. This will not perform well if you will be inserting lots of rows into the images table.Another option is to write a user-defined function that searches the tables and returns a 1 if it finds it, and use that in a check constraint. The performance will likely be as bad or worse than the trigger.Best suggestion I have is to consolidate those tables into one, if that's an option for you. |
 |
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2009-05-07 : 08:39:51
|
| Thanks robvolk1. In webSite db i use sql server, but in client db, i use Access 2007, hence i can't use trigger.2. I don't want to search tablles, because as u saied, it's performance is very slow.3. i can't merge tables because table structures is different.what about u if each table have it's own image column ?Any other idea ? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-07 : 09:30:36
|
| I've never stored images in the database and never recommended it, but it's up to you if you want to add them to the tables. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-05-07 : 13:37:23
|
| If you have similar tables with different attributes you might try looking at the supertype/subtype construct. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-07 : 21:31:11
|
| Jeff's article has details on how to do such a thing:http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server |
|
|
|
|
|
|
|
|
How to hold images in table which related tables ?