 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / DB Theory / August 2008Multiple tables refer to one -To use foreign keys or not?
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano > As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), > my colleague and I have designed the following (it's a part of a [quoted text clipped - 31 lines] > Saludos, > Adriano I will cite Date's _Principle of Incoherence_ and ask whether you are re-inventing EAV, yet again? > I will cite Date's _Principle of Incoherence_ and ask whether you are > re-inventing EAV, yet again? Reading http://en.wikipedia.org/wiki/Entity-Attribute-Value_model , but this doesn't look like the same problem at first glance. I'll try to wrap my head around the concept. Though it does seem to be an awful lot of work for this particular task. Thanks for the readup anyway, if it's the right solution, good, and if it's not, I'll avoid reinventing stuff in the future. Saludos, Adriano >> I will cite Date's _Principle of Incoherence_ and ask whether you are >> re-inventing EAV, yet again? [quoted text clipped - 7 lines] >Thanks for the readup anyway, if it's the right solution, good, and if >it's not, I'll avoid reinventing stuff in the future. It is not the right solution. It is a hideous way to do it. Sincerely, Gene Wirchenko Computerese Irregular Verb Conjugation: I have preferences. You have biases. He/She has prejudices. >>>I will cite Date's _Principle of Incoherence_ and ask whether you are >>>re-inventing EAV, yet again? [quoted text clipped - 9 lines] > > It is not the right solution. It is a hideous way to do it. And I want to make clear I wasn't accusing the OP of anything hideous. I could not make enough sense of his post to tell what he was doing, and some of it seemed suggestive of yet another reinvention of EAV. Not enough to say for sure, though. > >>>I will cite Date's _Principle of Incoherence_ and ask whether you are > >>>re-inventing EAV, yet again? [quoted text clipped - 14 lines] > some of it seemed suggestive of yet another reinvention of EAV. Not > enough to say for sure, though. A simple "Your post is very confusing, please clarify X, Y and Z" would have been better. Still, I'm not offended by people telling me I'm doing things the worst way, or telling me what I wrote is confusing. I half suspect it anyway. This is very new to me. As I said, EAV is an interesting concept, and one I'd rather know, so thanks for pointing it out, even if it's not related to this. Saludos Adriano [snip] >A simple "Your post is very confusing, please clarify X, Y and Z" >would have been better. A clear post in the first place would be even better. I have things to do with my time. If my first impression of a post is that I can not easily understand it, why should I bother with it? >Still, I'm not offended by people telling me I'm doing things the >worst way, or telling me what I wrote is confusing. >I half suspect it anyway. This is very new to me. > >As I said, EAV is an interesting concept, and one I'd rather know, so >thanks for pointing it out, even if it's not related to this. I would rather no -- as in "NO WAY!" -- EAV. sincerely, Gene Wirchenko Computerese Irregular Verb Conjugation: I have preferences. You have biases. He/She has prejudices. > [snip] > [quoted text clipped - 4 lines] > things to do with my time. If my first impression of a post is that I > can not easily understand it, why should I bother with it? All well and good, but why reply in that case? Saludos, Adriano >>[snip] >> [quoted text clipped - 6 lines] > > All well and good, but why reply in that case? Because in some rare instances one can help the other person by replying. I have no idea how open minded, intellectually honest and responsible you are until I broach the subject of the incoherence. >>>>>I will cite Date's _Principle of Incoherence_ and ask whether you are >>>>>re-inventing EAV, yet again? [quoted text clipped - 17 lines] > A simple "Your post is very confusing, please clarify X, Y and Z" > would have been better. No, actually, it wouldn't have. You might have preferred it, but it would have done you a lot more harm than good. > Still, I'm not offended by people telling me I'm doing things the > worst way, or telling me what I wrote is confusing. [quoted text clipped - 5 lines] > Saludos > Adriano The only really useful and helpful thing I can say is: You need to learn the fundamentals before you start designing databases. And learning the fundamentals has nothing to do with learning the SQL language. > >>>>>I will cite Date's _Principle of Incoherence_ and ask whether you are > >>>>>re-inventing EAV, yet again? [quoted text clipped - 20 lines] > No, actually, it wouldn't have. You might have preferred it, but it > would have done you a lot more harm than good. Ok, then this was a better reply than your original one. Though I don't understand how asking me to rephrase my problem (thus rethinking it) would do more harm. > The only really useful and helpful thing I can say is: You need to learn > the fundamentals before you start designing databases. And learning the > fundamentals has nothing to do with learning the SQL language. Yes, this is clear. I have the impression, though, that learning some of the fundamentals implies a bit of experience in most real-life cases. I could be wrong. Saludos Adriano >>>>>>>I will cite Date's _Principle of Incoherence_ and ask whether you are >>>>>>>re-inventing EAV, yet again? [quoted text clipped - 24 lines] > don't understand how asking me to rephrase my problem (thus rethinking > it) would do more harm. It doesn't matter how many times one rephrases things if one lacks the basic understanding required for coherent analysis. Even if the result sounds coherent, it's probably accidental, and the chances for mutual comprehension are extremely low. >>The only really useful and helpful thing I can say is: You need to learn >>the fundamentals before you start designing databases. And learning the [quoted text clipped - 3 lines] > of the fundamentals implies a bit of experience in most real-life > cases. I could be wrong. Fundamentals first. Experience later. A person can work in this field for 20 years and gain a year's experience 20 times, or a person can work in this field for 20 years and gain 20 years experience. Sadly, one can find too many of the former and too few of the latter. In fact, the former abound. >Fundamentals first. Experience later. A person can work in this field >for 20 years and gain a year's experience 20 times, or a person can work >in this field for 20 years and gain 20 years experience. > >Sadly, one can find too many of the former and too few of the latter. In >fact, the former abound. Adriano I stongly suspect Bob views me in the former category. Which doesn't bother me in the slightest. Bob has marked my postings to be ignored so he won't read my reply. As far as I know he is the only person who has ever kill filed me in the newsgroups. Judging by his other replies though I view his action as a badge of honour and worthy of distinction. Tony  SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ >>Fundamentals first. Experience later. A person can work in this field >>for 20 years and gain a year's experience 20 times, or a person can work >>in this field for 20 years and gain 20 years experience. >> >>Sadly, one can find too many of the former and too few of the latter. In >>fact, the former abound. >I stongly suspect Bob views me in the former category. Which doesn't bother me in >the slightest. [quoted text clipped - 3 lines] >his other replies though I view his action as a badge of honour and worthy of >distinction. Why how precious. Plonked. Sincerely, Gene Wirchenko Computerese Irregular Verb Conjugation: I have preferences. You have biases. He/She has prejudices. >>Bob has marked my postings to be ignored so he won't read my reply. As far as I >>know he is the only person who has ever kill filed me in the newsgroups. Judging by >>his other replies though I view his action as a badge of honour and worthy of >>distinction. > > Why how precious. Plonked. I've now been plonked by two people in this newsgroup. Whoohooo!! Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), > my colleague and I have designed the following (it's a part of a [quoted text clipped - 28 lines] > By all means, if this is a WTF, point it out, the reason, and possibly > an alternative. If I understand your intention correctly then I am sorry to say it probably is a WTF. I say "probably" because you actually describe two intentions. In one place you talk about relating objects (sic) to attachments, and immediately after that you talk about doing the opposite. I suspect you are uncomfortable with the idea of multiple attachment tables because superficially they would look the same (in the sense they'd have the same column names, each of the same type). That is a programmer's point of view. To a database designer they should appear to be VERY different and I suspect that if you look deep into the logic of what one is justified in doing with attachments for people versus attachments for works_of_art, those differences will become apparent. Once you see that you will be perfectly happy to have multiple attachment tables. The physical model would correspond closely to the logical model. That makes life easy because it makes it hard to write a correctly formed query that executes correctly and yet gives a wrong or meaningless answer. Database design is not about making it easy to do correct things so much as it is about making it hard to inadvertently do wrong things, and rather paradoxically (it seems to me) that often ends up making correct things easy into the bargain. Having offered an opinion, I ought to say you'd be unwise to follow any specific advise you get from a newsgroup. We all have our own enthusiasms, prejudices, and preconceptions of what you are trying to do. Roy > If I understand your intention correctly then I am sorry to say it probably > is a WTF. I say "probably" because you actually describe two intentions. > In one place you talk about relating objects (sic) to attachments, and > immediately after that you talk about doing the opposite. My bad. Attachments belong to objects, not otherwise (at least, not in this design). > I suspect you are uncomfortable with the idea of multiple attachment tables > because superficially they would look the same (in the sense they'd have the [quoted text clipped - 5 lines] > happy to have multiple attachment tables. The physical model would > correspond closely to the logical model. This is a _very_ useful and enlightening answer. Yet again, by your words below... > That makes life easy because it makes it hard to write a correctly formed > query that executes correctly and yet gives a wrong or meaningless answer. [quoted text clipped - 6 lines] > specific advise you get from a newsgroup. We all have our own enthusiasms, > prejudices, and preconceptions of what you are trying to do. I'm trying to inform myself, and I have to do it on the run (i.e. not enough time to do it right and study SQL properly, plenty to do it over :) ). So I'll take my chances. Of course, I do try to think before I implement anything. Thanks. Adriano >We have an "attachments" table, which can refer to "works_of_art", >"people", "reproductions" (Let's say, more than three tables). So a >work of art can have attachments, a person can have a different >attachment, etc. What is an attachment? I can understand the works of art, people and reproductions but I don't know what you mean by attachment. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > >We have an "attachments" table, which can refer to "works_of_art", > >"people", "reproductions" (Let's say, more than three tables). So a [quoted text clipped - 3 lines] > What is an attachment? I can understand the works of art, people and reproductions > but I don't know what you mean by attachment. An image, document or other file related to the entity in question. For a work_of_art, an image of a reproduction, a pdf copy of the invoice would be attachments. For a reproduction, a scan of the page in question where the work_of_art was reproduced (this is contrived, yes), for a person, a picture, a music file... Saludos Adriano >> >We have an "attachments" table, which can refer to "works_of_art", >> >"people", "reproductions" (Let's say, more than three tables). So a [quoted text clipped - 9 lines] >in question where the work_of_art was reproduced (this is contrived, >yes), for a person, a picture, a music file... Ah, a file attachment. Too obvious. <smile> I did almost exactly what you propose in a fleet management system. I have one table with a primary autonumber field (this is in Access), foreign keys pointing to the two other equipment and service order tables, a user entered date and description of attachment and the file extension. An example of a file associated with a unit might be a scan of the vehicle registration, commercial inspection by a licensed mechanic or photographs. An example of the file associated with a service order might be a scan of a external invoice for windshield change or transmission work.. Or maybe some photos of the failed part. The user was presented with a form listing all the files in their Incoming folder. They could view the attachments or process them. Processing involved associating the attachment with a particular unit or a service done on that unit. I then moved the file from their incoming folder into a network folder. I also renamed the file so it had the same value as the table's autonumber key although with leading zeros. The file extension is required so the operating system can choose whatever software the user has installed to view that file. I also had some fields allowing the user to optionally choose photo's which will be viewed in the app screens and on selected reports. Now anyone can go into the system and view all scanned documents, photo's or other files assocated with each unit or service. No need to rummage around various file folders looking for specific invoices or other documents. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), > my colleague and I have designed the following (it's a part of a [quoted text clipped - 18 lines] > solution, and that adding a new type of "attachee" was simpler/more > efficient than adding a new foreign key. Don't do this. It's been tried thousands of times. It nearly always leads to chaos. The general principle is: don't put data and metadata side by side in the same table. I have violated this principle in my own private tools, but never in work to be used by others. If I understand your problem correctly then the constraints that you desire are called "distributed foreign keys" and they're described at http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf The snag is that no DBMS on the market today supports them in a simple declarative way like they do with the SQL "FOREIGN KEY" syntax. You can implement them using triggers, which is described in: Koppelaars, T. and de Haan, L. Applied Mathematics for Database Professionals. Apress. 2007. Regards, Adrian > As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), > my colleague and I have designed the following (it's a part of a [quoted text clipped - 31 lines] > Saludos, > Adriano On Jun 2, 2:01 am, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote: > Gene Wirchenko <ge...@ocis.net> wrote: > >>Bob has marked my postings to be ignored so he won't read my reply. As far as I [quoted text clipped - 5 lines] > > I've now been plonked by two people in this newsgroup. Whoohooo!! I gotta be honest, I couldn't see the relevance of you response to the OP's question. Roy's and Adrian's responses were good pointers, while the general warning away from EAV by everyone else was also apt. I'm not sure how your personal anecdote contributed to his trying to reference multiple relations from the same foreign key. > Tony > -- [quoted text clipped - 3 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm > Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/ To the OP - have you considered just using different tables for the attachments? Sure attachments are all of the same type but relations don't collectivize objects (a common misconception that has to be shaken off when moving from OO to RM). Relations collectivize propositions of a common form - and yours seem to be different. Regards, J. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.