tag:blogger.com,1999:blog-6393051114813114443.post6170331950535994078..comments2024-01-24T15:38:09.758-05:00Comments on Lee's Blog: Referential Integrity - Data Modeling Mistake 1 of 10Lee Richardsonhttp://www.blogger.com/profile/01314803491511307042noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-6393051114813114443.post-7302263598736594772012-10-02T05:35:02.001-05:002012-10-02T05:35:02.001-05:00please tell me how should I develop a mini databas...please tell me how should I develop a mini database project on population management.. Im little confused and scared since Im doing it alone.. give some idea.priyanoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-8883208863750637052009-01-09T12:37:00.000-05:002009-01-09T12:37:00.000-05:00Quote from the previous - "The right way to to imp...Quote from the previous - <BR/>"The right way to to implement a polymorphous relationship is a string field `commentable_type` that contains the name of the table the foreign key `commentable_id` points to. That's flexible, DRY and does without magic numbers, while conserving referential integrity."<BR/><BR/>This DOES NOT conserve referential integrity. That is the whole point. You CANNOT define a foreign key constraint on a column that maps to ids in multiple tables.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-70277497946713595182007-12-09T07:31:00.000-05:002007-12-09T07:31:00.000-05:00Do you really want to add a column for every new t...Do you really want to add a column for every new type of commentable, and have hardcoded switches all over your code? <BR/><BR/>The right way to to implement a polymorphous relationship is a string field `commentable_type` that contains the name of the table the foreign key `commentable_id` points to. That's flexible, DRY and does without magic numbers, while conserving referential integrity.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-84873720847915676042007-08-27T07:36:00.000-05:002007-08-27T07:36:00.000-05:00I agree with most of the things pointed out here, ...I agree with most of the things pointed out here, but I have to add that the "database is the foundation" approach is a mistake also and a big one. The business process (what to do and then how to do) should be the "foundation" of the application and the database should serve the application just as any other part do. Database centric approaches are one of the very most causes of bad design in the first place.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-91129074990609080512007-07-23T04:17:00.000-05:002007-07-23T04:17:00.000-05:00If there are 200 tables which need to be reference...If there are 200 tables which need to be referenced by a pure key field in the same table, that means you have 200 entities with common functionality. In this case you should probably put some multi table inheritance in place so you can make a decent OO representation of these 200 entities. <BR/><BR/>This way the 200 tables all reference one parent table which contains a unique id which can be used as the foreign key.Alexander Malfaithttps://www.blogger.com/profile/04897760061888752985noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-14245011818386856482007-07-20T16:08:00.000-05:002007-07-20T16:08:00.000-05:00Carlos Alberto Costa: I'm not sure I understand. ...Carlos Alberto Costa: I'm not sure I understand. Could you provide an example?<BR/><BR/>Incidentally, if by composite key you mean more than one natural key making up the primary key then I'll tell you all about how I disagree with that in my next post on surrogate keys. I suspect this view will be a little more controversial than the RE one.Lee Richardsonhttps://www.blogger.com/profile/01314803491511307042noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-18153472121539135892007-07-20T16:03:00.000-05:002007-07-20T16:03:00.000-05:00"On #4...what if there are 200 tables? Then what?"..."On #4...what if there are 200 tables? Then what?"<BR/><BR/>Bradrick Manor: Thanks, I couldn't have said it better myself.Lee Richardsonhttps://www.blogger.com/profile/01314803491511307042noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-66878125965157109242007-07-20T16:02:00.000-05:002007-07-20T16:02:00.000-05:00McGurk: If I had a nickle for every time you make ...McGurk: If I had a nickle for every time you make me laugh... Sorry to hear about the work situation, I feel for you.Lee Richardsonhttps://www.blogger.com/profile/01314803491511307042noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-38998795260460018082007-07-20T15:15:00.000-05:002007-07-20T15:15:00.000-05:00On item 4, what should I do if my database has com...On item 4, what should I do if my database has composite keys and those fields are part or the primary key or if the table is an association table in an n to n association?Anonymoushttps://www.blogger.com/profile/07811355318730720087noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-75487893165554852992007-07-20T14:27:00.000-05:002007-07-20T14:27:00.000-05:00"On #4...what if there are 200 tables? Then what?"..."On #4...what if there are 200 tables? Then what?"<BR/>This is a very vague question. I have very rarely seen this type of situation and I was able to come up with another solution that still allowed me to use a foreign key. Bottom line though, if you have to, create 200 columns. This will be a lot less work then trying to diagnose a correct an issue later. <BR/><BR/>Do you actually have this problem, or are just being argumentative?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-92130103913665995752007-07-20T13:13:00.000-05:002007-07-20T13:13:00.000-05:00On #4...what if there are 200 tables? Then what?On #4...what if there are 200 tables? Then what?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-27798806402082477352007-07-20T12:43:00.000-05:002007-07-20T12:43:00.000-05:00Reason #5: "Because SQL is hard! And ORMs keep all...Reason #5: "Because SQL is hard! And ORMs keep all my RI in the application!"<BR/><BR/>Seriously(/sadly), I have heard this one all too often recently.MattKhttps://www.blogger.com/profile/03232119738920744507noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-59270854127311800602007-07-19T15:01:00.000-05:002007-07-19T15:01:00.000-05:00Nice article and I agree 100% with your points.RE ...Nice article and I agree 100% with your points.<BR/><BR/>RE is essential, all the more so when on an Agile project. I would highly stress making any database system as RIGID as possible when it comes to columns and tables.<BR/><BR/>In the future you can always relax these constraints when necessary. It's more akin to incremental programming in my mind.Brett Veenstrahttps://www.blogger.com/profile/09897293833393658648noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-82370375886559008432007-07-19T08:16:00.000-05:002007-07-19T08:16:00.000-05:00I agree with you 100%. Only fuckups don't use RE ...I agree with you 100%. Only fuckups don't use RE in their databases. <BR/><BR/>I'm currently working on a web frontend for a software product with a massive backend. We're talking about two central databases with over 80 tables, and as the installation grows additional databases are added, each of those having about 30 tables, some of which contain FK's from the primary two databases. None of these databases have RE enforced. And none of the CrUD queries of the main program are run in transactions, so orphans are an every day occurrence. <BR/><BR/>Whereas Reason #1 may seem to be correct in respects to avoiding premature optimization, this project started out exactly this way. And now we have clients with 30-40 odd databases in one system, each one with FK's from the main database, none of which having any RE at all. Its a complete clusterfuck.<BR/><BR/>Reason 4 is also something that should NEVER EVER be done. This same project has a field in a table which, when int column X is a certain value, is a FK for one of the security tables, and when column X is a different value, is a long value used for sorting and other functions. How fucked up is that? People who do 4 should be flayed alive and staked out over a fire ant hill.<BR/><BR/>I think you can sum up this whole post with one simple statement: Don't let idiots design your database.Anonymousnoreply@blogger.com