SQL tables question

[Neo Geshel]

I am looking to improve redundancy in a database. If I have several
different tables having identical child tables (design, not data) for
holding images, is it possible to have all these different tables
reference a single common child table to hold their images? If so, how
do I maintain referential integrity? I have thought about creating an
additional column in the image table called MasterTBL (beside ID,
MasterID & IMG) to hold the name of the table the image is associated
with, but this doesn't seem to be an option with Access' built-in
referential integrity methods (Access doesn't seem to be able to use a
table name as an integrity method).

Does anyone have any suggestions or on-line examples that I can look over?

TIA
...Geshel
--
*************** *************** *************** *************** ***********
* My reply-to is an automatically monitored spam honeypot. Do not use *
* it unless you want to be blacklisted by SpamCop. Please reply to my *
* first name at my last name dot org. *
*************** *************** *************** *************** ***********
“Anyone who believes in Intelligent Design (“creationism �) is just as
ignorant and ill-educated as someone who believes that the world is
flat, that the Sun circles the Earth or that there really is a tooth
fairy. Darwinism has an overwhelming foundation of evidence that can be
tested and reproduced. Intelligent Design, on the other hand, has no
evidence at all; not one single shred of testable proof. As such,
Intelligent Design is Religious Mythology, and has no right whatsoever
to be in our Science classrooms.� - 99.99+% of Scientists
*************** *************** *************** *************** ***********
Mignon McLaughlin once said that “A nymphomaniac is a woman [who is] as
obsessed with sex as the average man.� Unfortunately, since true
nymphomaniacs are so rare, this means that it takes an extraordinary
woman to keep up with an ordinary man.
*************** *************** *************** *************** ***********

[Karl Seguin]

Well, I still think the other branch of this thread wasn't horrible advice
:P

karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
http://openmymind.net/redirector.aspx?documentId=51 - Learn about AJAX!

"Jason Kester" <ja*********@gm ail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

First off, let me apologize for the asonishingly terrible advice you've
been getting in this thread. We're normally a bit sharper than this!
Maybe if you'd asked over in a SQL Server group...

But yeah, please don't simply abandon normalization and add TableName
to your Image table! There's a way out of this.

If you have a common base for all the tables that you need to hook
Images up to, you're could do something like:
Company
----
CompanyID
CompanyName, etc...
Customer
---
CompanyID
ShippingAddress , etc...
Manufacturer
---
CompanyID
ManufacturerSpe cificStuff...
So, you'd generate your IDs in Company, then reuse them in Customer of
Manufacturer. Essentially making Company the base class for Customer
and Manufacturer. No need to define a CustomerID, since it's
one-to-one with Company.

Then you can have a CompanyImages table that takes advantage of the
fact that CompanyID is unique across Company, Customer, Manufacturer,
Supplier, etc. Thus allowing your image table to look like:

CompanyImage
---
CompanyID
ImageID
One table to associate images to anything that has Company as its base.
Make sense? Just make sure that your sub-classes are actually related
to one another, or you're setting yourself up for and even sketchier
data-integrity nightmare than you would if you tried to cram everything
into a single table with a TableName column.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

[Al Reid]

"Jason Kester" <ja*********@gm ail.com> wrote in message news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

Al Reid wrote:

Yes you can do it. Create a field named STID (source Table or something more to your liking). Store the name of the table in that field. Delete any primary key you currently have defined. In the Design View, select the new field and the old primary key field and on the toolbar, click the primary key button. You now have a composite key.

When you add the records, store the table name in the STID field. Include the STID in your table joins and queries.

I hope this helps,

Holy crap! Did you just say that out loud? That's a Terrible Idea!
Sure, you can build a system where your entire database fits into a
single table that way, but you're setting yourself up for a maintenance
nightmare! Just think about how you would need to set your foreign
keys up to handle this. You would essentially need to add STID onto
EVERY RECORD in EVERY TABLE INVOLVED:

CompanyID CompanyName TableName
1 Joes pizza "I'm from the Company Table"
2 Al pizza "I'm from the Company Table"
3 steve's pizza "I'm from the Company Table"
4 Jims pizza "I'm from the Company Table"

ImageID KeyID TableName
1 1 "I'm from the Company Table"
2 1 "I'm from the Company Table"
1 3 "I'm from the Shipping Table"
4 5 "I'm from the Shipping Table"
Please Please Please don't do this!
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

The OP asked for suggestions and I provided one. The OP can decide if there is a compelling reason to do what he is asking or if it
is wise to do it. The OP also stated that it could not be done in Access. I showed that it can be done.

I have worked on systems where the concept of using a single table to hold image or object data was employed. The nice thing about
it is that there are not multiple tables with identical structures holding like data. Having a single table that holds identical
data does not, in my opinion, make it any more difficult to maintain. One system (that I did not design) used a single "Objects"
table along with a single "mapping" table for all data base links. The Mapping table had columns for source table, source row,
destination table, destination row, link type and link description. This was the only table that included table names as a column.
At first I thought it was a terrible idea, but the beauty is that the same database schema can be ported to any relational database
without any changes whatsoever. In fact, the schema and data can be transferred, the connection string changed and it works. For
example, one can use the MSSQL DTS to copy the schema from Oracle to MSSQL, edit the connection string and run the application. No
other changes need to be made. Given that the goal was to have database independence, the design was reasonable, if not optimal.
Would I design it that way? Probably not. YMMV.

--
Al Reid