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.
*************** *************** *************** *************** *********** 11 1278
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!
"Neo Geshel" <go****@geshel. org> wrote in message
news:OJ******** *****@TK2MSFTNG P09.phx.gbl...
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 ("creationis m") 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 wrote: 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.
*************** *************** *************** *************** ***********
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
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.
"Neo Geshel" <go****@geshel. org> wrote in message
news:Ox******** ******@TK2MSFTN GP14.phx.gbl...
Karl Seguin wrote: 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 ("creationis m") 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.
*************** *************** *************** *************** ***********
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
"Neo Geshel" <go****@geshel. org> wrote in message news:OJ******** *****@TK2MSFTNG P09.phx.gbl...
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 ("creationis m") 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.
*************** *************** *************** *************** ***********
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!
"Adrian Parker" <ap******@nospa m.nospam> wrote in message
news:uI******** ********@TK2MSF TNGP12.phx.gbl. .. 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.
"Neo Geshel" <go****@geshel. org> wrote in message news:Ox******** ******@TK2MSFTN GP14.phx.gbl... Karl Seguin wrote: 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 ("creationis m") 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. *************** *************** *************** *************** ***********
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 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/
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Steve Klett |
last post by:
(I posted this in ADO group, but I think this group will be better)
Hi-
I need to develop an FAQ section for our website. We would like to break up
the FAQ by products, then categories with each category having n
question/answer pairs.
I would like to pass parameters in the querystring based on what the
product/category the user selected, then populate a datagrid with the
correct set of question/answer
|
by: Gary Peek |
last post by:
Can anyone tell us the browsers/versions that exhibit errors
when tables are nested too deeply? And how many levels of
nesting produces errors?
(not a tables vs CSS question)
|
by: Simon Dean |
last post by:
Duh...
Not another one...
Hopefully simple though, I hate the way *I* (and it might be a CSS
trait) can't intermix fixed width divisional elements with a variable
auto expanding div???
The great thing I loved with Tables, was being able to say:
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
by: cefrancke |
last post by:
I have a few tables with sensitive user information (passwords, etc.)
and I would like to prevent someone from opening a blank database and
importing those tables.
Is there a way to "hide" or protect these tables from being linked or
imported?
TIA
| |
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |