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]

Maybe I'm being dense, but why not just add an ImageId to all those tables?

Table:User
userName
password
imageId

Table:Group
groupName
imageId

Table:house
address
imageId

Table:Image
ImageId
Path


select U.UserName, U.Password, I.Path
from user u inner join image i on u.imageId = i.imageId
where Username = "jon"

Karl

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



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.
***********************************************************************

 

[Neo Geshel]

Maybe I'm being dense, but why not just add an ImageId to all those tables?

Table:User
userName
password
imageId

Table:Group
groupName
imageId

Table:house
address
imageId

Table:Image
ImageId
Path


select U.UserName, U.Password, I.Path
from user u inner join image i on u.imageId = i.imageId
where Username = "jon"

Karl

This wouldn't work because there are going to be multiple images in the
child table for each master table. That, and each master table is
different. Some only have a title and a comment (memo field), and others
have detailed info, including images of their own (for a section that
has a "eyecatch" image, that eyecatch image would be in the master
table, whereas all of the secondary images would end up in the child table).

The point is, is it possible for each master table to access multiple
entries in a single, shared child table? That is, you have several
master tables, each with wildly differing content, each with multiple
entries. Each entry in each master table (a "master entry") then
references a particular subset of images (multiple images for each
master entry) in a common child table.

So, for example:

Table 1:
ID Name
1 John
2 Mary
3 Sue

Table 2:
ID Name
1 Copenhagen
2 Toronto
3 Vancouver

Table 3:
ID MasterID Image
1 1(John) Image 1
2 1(John) Image 2
3 2(Mary) Image 1
4 1(Copenhagen) Image 1
5 1(John) Image 3
6 3(Vancouver) Image 1
7 1(Copenhagen) Image 2
8 3(Sue) Image 1

As you can see above, the MasterID runs into a major problem with a
common child table, because a value of 1 could mean either John (if we
are talking about table 1) or Copenhagen (if we are talking about table
2). The problem is, the DB is dumb. It can't tell the difference after
the data has been inputted. And I just can't use the Name fields from
the master tables, because then an association has to be made between
the TYPE of value being used (name of person or name of city) and the
table needing to be referenced. And what if Table 2 didn't use names but
dates? Then what? You can't put date values and name values in the same
column (in table 3) without some major headaches.

Normally, each master table would have its own child table (to hold
multiple images per master), but since all of these child tables have
the same kinds of fields (an Autonumbered ID, MasterID, OLE Object and a
Memo field), wouldn't it be more efficient for multiple master tables to
access the same child table? If so, then how?

BTW, the images are being stored IN the database, but my method (using
*only* an OLE Object field) seems to work well for displaying them as well

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.
***********************************************************************

 

[Cor Ligthert [MVP]]

Neo,

How would you do it if it where instead of Images, article numbers.

I don't see the difference.

A car has mostly four wheels mostly all four wheels the same, sometimes are
the front wheels the same and the back wheels the same while there can be a
sparewheel that has a different size.

I don't see the difference with the way you described your problem, the
wheels would be hold in one table.

Just my thought,

Cor

 

[Adrian Parker]

Flip the naming of the IDs around.. When creating the entry in the image
table, add a unique ID there then store that unique ID in the table that
references it.

Maybe I'm being dense, but why not just add an ImageId to all those
tables?

Table:User
userName
password
imageId

Table:Group
groupName
imageId

Table:house
address
imageId

Table:Image
ImageId
Path


select U.UserName, U.Password, I.Path
from user u inner join image i on u.imageId = i.imageId
where Username = "jon"

Karl

This wouldn't work because there are going to be multiple images in the
child table for each master table. That, and each master table is
different. Some only have a title and a comment (memo field), and others
have detailed info, including images of their own (for a section that
has a "eyecatch" image, that eyecatch image would be in the master
table, whereas all of the secondary images would end up in the child table).

The point is, is it possible for each master table to access multiple
entries in a single, shared child table? That is, you have several
master tables, each with wildly differing content, each with multiple
entries. Each entry in each master table (a "master entry") then
references a particular subset of images (multiple images for each
master entry) in a common child table.

So, for example:

Table 1:
ID Name
1 John
2 Mary
3 Sue

Table 2:
ID Name
1 Copenhagen
2 Toronto
3 Vancouver

Table 3:
ID MasterID Image
1 1(John) Image 1
2 1(John) Image 2
3 2(Mary) Image 1
4 1(Copenhagen) Image 1
5 1(John) Image 3
6 3(Vancouver) Image 1
7 1(Copenhagen) Image 2
8 3(Sue) Image 1

As you can see above, the MasterID runs into a major problem with a
common child table, because a value of 1 could mean either John (if we
are talking about table 1) or Copenhagen (if we are talking about table
2). The problem is, the DB is dumb. It can't tell the difference after
the data has been inputted. And I just can't use the Name fields from
the master tables, because then an association has to be made between
the TYPE of value being used (name of person or name of city) and the
table needing to be referenced. And what if Table 2 didn't use names but
dates? Then what? You can't put date values and name values in the same
column (in table 3) without some major headaches.

Normally, each master table would have its own child table (to hold
multiple images per master), but since all of these child tables have
the same kinds of fields (an Autonumbered ID, MasterID, OLE Object and a
Memo field), wouldn't it be more efficient for multiple master tables to
access the same child table? If so, then how?

BTW, the images are being stored IN the database, but my method (using
*only* an OLE Object field) seems to work well for displaying them as well

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.
***********************************************************************

 

[Al Reid]

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,

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

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]

Agreed.

And if you need many-to-many joins, I'd simply go the route of creating a
mapping table for each parent table.


UserImageJoin
UserId, ImageId

GroupImageJoin
UserId, ImageId


Karl

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

Flip the naming of the IDs around.. When creating the entry in the image
table, add a unique ID there then store that unique ID in the table that
references it.

Maybe I'm being dense, but why not just add an ImageId to all those
tables?

Table:User
userName
password
imageId

Table:Group
groupName
imageId

Table:house
address
imageId

Table:Image
ImageId
Path


select U.UserName, U.Password, I.Path
from user u inner join image i on u.imageId = i.imageId
where Username = "jon"

Karl

This wouldn't work because there are going to be multiple images in the
child table for each master table. That, and each master table is
different. Some only have a title and a comment (memo field), and others
have detailed info, including images of their own (for a section that
has a "eyecatch" image, that eyecatch image would be in the master
table, whereas all of the secondary images would end up in the child
table).

The point is, is it possible for each master table to access multiple
entries in a single, shared child table? That is, you have several
master tables, each with wildly differing content, each with multiple
entries. Each entry in each master table (a "master entry") then
references a particular subset of images (multiple images for each
master entry) in a common child table.

So, for example:

Table 1:
ID Name
1 John
2 Mary
3 Sue

Table 2:
ID Name
1 Copenhagen
2 Toronto
3 Vancouver

Table 3:
ID MasterID Image
1 1(John) Image 1
2 1(John) Image 2
3 2(Mary) Image 1
4 1(Copenhagen) Image 1
5 1(John) Image 3
6 3(Vancouver) Image 1
7 1(Copenhagen) Image 2
8 3(Sue) Image 1

As you can see above, the MasterID runs into a major problem with a
common child table, because a value of 1 could mean either John (if we
are talking about table 1) or Copenhagen (if we are talking about table
2). The problem is, the DB is dumb. It can't tell the difference after
the data has been inputted. And I just can't use the Name fields from
the master tables, because then an association has to be made between
the TYPE of value being used (name of person or name of city) and the
table needing to be referenced. And what if Table 2 didn't use names but
dates? Then what? You can't put date values and name values in the same
column (in table 3) without some major headaches.

Normally, each master table would have its own child table (to hold
multiple images per master), but since all of these child tables have
the same kinds of fields (an Autonumbered ID, MasterID, OLE Object and a
Memo field), wouldn't it be more efficient for multiple master tables to
access the same child table? If so, then how?

BTW, the images are being stored IN the database, but my method (using
*only* an OLE Object field) seems to work well for displaying them as well

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.
***********************************************************************

 

[Jason Kester]

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
ManufacturerSpecificStuff...


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/

 

[Jason Kester]

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/

 

[Cor Ligthert [MVP]]

Jason,

What you describe in your last table is a part of the relative database
concept.

It is about 25 years ago relaced by the relational database.
That relative concept is almost not to maintanance.

Cor

 

[Karl Seguin]

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


karl

 

[Al Reid]

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/

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.