|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL tables questiondifferent 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. *********************************************************************** 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" <got***@geshel.org> wrote in message I am looking to improve redundancy in a database. If I have severalnews:OJMdaJx6FHA.736@TK2MSFTNGP09.phx.gbl... 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 wrote:
Show quote > Maybe I'm being dense, but why not just add an ImageId to all those tables? This wouldn't work because there are going to be multiple images in the > > 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 > 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" <got***@geshel.org> wrote in message Karl Seguin wrote:news:OxNuYRy6FHA.2012@TK2MSFTNGP14.phx.gbl... Show quote > Maybe I'm being dense, but why not just add an ImageId to all those This wouldn't work because there are going to be multiple images in the> 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 > 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. *********************************************************************** 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 -- Show quoteMY ASP.Net tutorials http://www.openmymind.net/ http://openmymind.net/redirector.aspx?documentId=51 - Learn about AJAX! "Adrian Parker" <apparker@nospam.nospam> wrote in message news:uI6n%23q06FHA.2384@TK2MSFTNGP12.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" <got***@geshel.org> wrote in message > news:OxNuYRy6FHA.2012@TK2MSFTNGP14.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 ("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. > *********************************************************************** > 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" <got***@geshel.org> wrote in message news:OJMdaJx6FHA.736@TK2MSFTNGP09.phx.gbl... I am looking to improve redundancy in a database. If I have severaldifferent 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. *********************************************************************** 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 Holy crap! Did you just say that out loud? That's a Terrible Idea!> 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, 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
Show quote
"Jason Kester" <jasonkes***@gmail.com> wrote in message news:1132293418.221962.207950@g43g2000cwa.googlegroups.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> 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/ > 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 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/ --- Get your own Travel Blog, with itinerary maps and photos! http://www.blogabond.com/ Well, I still think the other branch of this thread wasn't horrible advice
:P karl-- Show quoteMY ASP.Net tutorials http://www.openmymind.net/ http://openmymind.net/redirector.aspx?documentId=51 - Learn about AJAX! "Jason Kester" <jasonkes***@gmail.com> wrote in message news:1132292714.407567.150500@f14g2000cwb.googlegroups.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 > 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/ > > --- > Get your own Travel Blog, with itinerary maps and photos! > http://www.blogabond.com/ >  |
|||||||||||||||||||||||
Other interesting topics