tag:blogger.com,1999:blog-426922399870577072.post8158912217283903483..comments2024-03-28T08:13:24.785-04:00Comments on The Database Programmer: Table Design Patterns: Cross-Reference ValidationKenDownshttp://www.blogger.com/profile/11117175783163937575noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-426922399870577072.post-28408192025066998732023-12-26T13:18:16.302-05:002023-12-26T13:18:16.302-05:00Good information.Good information.python ai programminghttps://factored.ai/python-programming-language-ai/noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-8786166910172347032022-06-10T04:33:42.221-04:002022-06-10T04:33:42.221-04:00You made some good points there. I really think th...You made some good points there. I really think this excellent website wants much more consideration. I agree with many of the solid points made by the writer. I’ll be back day in and day for further new updates . Thanks for sharing. Please check my post <a href="https://morioh.com/p/7f1ab770eb5e" rel="nofollow">CPS Test</a> , and would love to hear back from you about the article .Tiffanie Penahttps://www.blogger.com/profile/01533551988818308937noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-75000228558097334062022-03-29T14:59:14.204-04:002022-03-29T14:59:14.204-04:00Hi there!
I had a very nice experience in your bl...<br />Hi there!<br /><br />I had a very nice experience in your blog, actually I found this post explanatory and informative, keep sharing the best content<br /><br />regards<br /><br />Salvatore from Visite as <a href="https://www.tourcataratas.com.br/" title="ingressos cataratas" rel="nofollow">Cataratas do Iguaçu</a> em Foz do Iguaçu, Paraná - Brasil.<br /><br />Thanks and take care<br />Cataratas do Iguaçuhttp://tourcataratas.com.brnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-27031938087699325842021-06-12T07:33:41.641-04:002021-06-12T07:33:41.641-04:00This is one of the most amazing blogs that I have ...This is one of the most amazing blogs that I have gone through. In case you are looking for quality Airlines services then take a tour at <a href="https://www.justcol.com/address/japan-airlines-hanoi-office/" rel="nofollow"> Japan Airlines Hanoi Office </a>. You won’t be disappointed. Hulu Activatehttps://www.blogger.com/profile/16916899094169801118noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-3061367564712519442020-09-29T01:45:28.628-04:002020-09-29T01:45:28.628-04:00Thankyou for posting this blog.Thankyou for posting this blog.norton.com/setuphttps://myblog-search.uk.com/norton-setup/noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-84211795848189542442020-05-27T09:21:07.695-04:002020-05-27T09:21:07.695-04:00Empire ICT Solutions Co. Ltd<br /><a href="https://empireict.net/" rel="nofollow">Empire ICT Solutions Co. Ltd</a>softnethttps://www.blogger.com/profile/10967950529413712494noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-63402713914491292692020-05-27T09:17:29.946-04:002020-05-27T09:17:29.946-04:00Softnet Solutions Ltd<a href="https://www.softnetsolutions.co.ke/" rel="nofollow">Softnet Solutions Ltd</a>softnethttps://www.blogger.com/profile/10967950529413712494noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-56875297294972881952020-01-08T05:30:20.713-05:002020-01-08T05:30:20.713-05:00Thankyou for sharing this imformation with us.
no...Thankyou for sharing this imformation with us.<br /><br /><a href="https://norton-setup.org.uk/" rel="nofollow">norton.com/setup</a>mia mintonhttps://www.blogger.com/profile/01624148557561629019noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-4207604010217710302020-01-03T01:28:29.192-05:002020-01-03T01:28:29.192-05:00Thanks for sharing this marvelous post. I m very p...Thanks for sharing this marvelous post. I m very pleased to read this article. <br /><br /><a href="https://www-officesetup.uk.net/" rel="nofollow">office.com/setup</a><br /><a href="http://ennorton.uk/" rel="nofollow">norton.com/setup</a><br /><a href="http://helpmcafee.uk.com/" rel="nofollow">mcafee.com/activate</a><br />mcafee.com/activatehttps://helpmcafee.uk.com/activate/noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-50521008885356037442018-06-30T05:49:17.855-04:002018-06-30T05:49:17.855-04:00Thank you for sharing very nice content
Sql serve...Thank you for sharing very nice content <br /><a href="https://onlineitguru.com/sql-server-dba-online-training-placement.html" rel="nofollow">Sql server DBA Online Trainig Hyderabad </a><br />rmouniakhttps://www.blogger.com/profile/06622438005105687926noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-17619326217539226792017-11-10T04:14:35.389-05:002017-11-10T04:14:35.389-05:00Hello there, thank you for sharing this useful inf...Hello there, thank you for sharing this useful information with us. We, at keraladatabasemarketing.in offer database services for corporate companies, organizations and educational institutions. You can look for <a href="https://www.keraladatabasemarketing.in/studentdatabase" rel="nofollow">Student Database</a>, NRI database, Business class and more. Visit today! PBN - GKhttps://www.blogger.com/profile/10669991156119051826noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-44774614003757640282017-04-08T12:51:12.231-04:002017-04-08T12:51:12.231-04:00India's finest decorative arts for luxury home...<br />India's finest decorative arts for luxury home furniture and interiors. Our collection is custom created for you by our experts. <a title=" shoe storage solutions" href="https://www.welfurn.com/" rel="nofollow"> shoe storage solutions</a>we have a tendency to bring the world's best to our doorstep. welfurn is leading Interior design company that gives exquisite styles excellence in producing and Quality standards. will give door-step delivery and can complete the installation at your home.taiseerhttps://www.blogger.com/profile/06640706068951962972noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-38353373952374658162017-03-28T09:10:24.750-04:002017-03-28T09:10:24.750-04:00your blog is very helpful for visitors and tourist...your blog is very helpful for visitors and tourists thanks.<br /><br /><a href="http://www.duxte.co.tz/accounting-software/quickbooks-tanzania" rel="nofollow">Accounting software in Tanzania</a>Stephanie Clarkhttps://www.blogger.com/profile/16245519261116188657noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-64572927280631126682017-03-07T02:41:46.519-05:002017-03-07T02:41:46.519-05:00A really useful read, thanks for posting.
Databas...A really useful read, thanks for posting.<br /><br /><a href="http://www.duxte.co.tz/services/website-design-tanzania" rel="nofollow">Database design Tanzania</a>Stephanie Clarkhttps://www.blogger.com/profile/16245519261116188657noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-67719132573015052822013-09-23T00:37:39.574-04:002013-09-23T00:37:39.574-04:00Hi,Nevertheless web design, search engine optimiza...Hi,Nevertheless web design, search engine optimization and copywriting is a really customized area. Design isn't really just what you see, it's also exactly what you think and feel as you browse a Web site Sure for <a href="http://www.theosoftindia.com/" rel="nofollow">Web Design Cochin</a>, discovering HTML is an important part of creating an effective web site.Thanks...Anonymoushttps://www.blogger.com/profile/09701377984223022528noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-14460060268648489912013-02-20T03:28:16.714-05:002013-02-20T03:28:16.714-05:00Hi Ken,
first of all:
- your blog is amazing - a ...Hi Ken,<br /><br />first of all:<br />- your blog is amazing - a fascinating source for inspiration. I just can't stop reading.<br />- I know, this article is an old one - however it is one that reflects your passion for character keys where it makes sense very much. So please forgive me, if I roll up old stuff.<br /><br />Your arguments for having character keys rather than number keys for refence data I understood as following:<br />1. codes are understandable to an extends which prevents joins (increased performance)<br />2. higher storage requirements for characters can be seen as minor issue as storage is cheaper than in past<br /><br />I wonder why the 'ressource is cheaper' argument does not play the INT key into hand as well. Regarding the mysqlperformanceblog you just have to plug in enough RAM into your system to allow >80% of your data being sql page cached there (I guess you know what I mean - if other readers do not: it is some sort of advanced caching). RAM is cheap and joins (especially with the right indeces in place) become extremly fast. I myself have implemented joins cross 10 and more tables (with at least one of them having more then a mio rows), that came up with results in a few milli seconds (the >80% in RAM applied there). I hear people screaming that there are occassions where you cannot have >80% in RAM. I know that - however these cases are very rare. In clouds you can plug 100 GB together with no worries and 100 GB allow some mio rows, which covers lots of use cases. <br /><br />The other thing I try to get sorted directly relates to the The Cross Reference Validation Pattern:<br />I understood from your article that cross reference uniqueness check can be done best be achieved with character keys because INT keys are not self explaining. The implied risk is, that there are duplicated names in the referenced tables. I am not sure how to address this via character codes. If you have a plant table with a plant 'broad been' and another one called 'fava been' - your character code may be 'brobeen' and 'favbeen'. It both refers to the same thing, which is thick beens, your character code will not be able to solve the risk of duplication. However if you have a mechanism to clean duplicates from the referenced tables you can also do the cross reference validation based on INT keys. Or did I miss something?<br /><br />There is also something related to this: While the time is running maturity of frameworks is increasing. Frameworks have to deal with the 'no duplicates in cross references'-on-int-key issue anyway, because they are not only dealing with reference data. But if the logic to do it is implemented in frameworks anyway and as frameworks become more popular what is the benefit of down cutting Cross Reference Validation to character keys?<br /><br />Please do not get me wrong here. I am not an INT key fighter. I see some benefits in using character code key. I just like to understand how different (strong) concepts fit together.<br /><br />Based on this would you now (5 years later) still implement character keys for reference data? Has the reasoning changed behind it?<br /><br />Cheers,<br />SteffenSteffennoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-86602128461778614632009-02-13T07:47:00.000-05:002009-02-13T07:47:00.000-05:00@anonymous: you are dramatically mixing contexts. ...@anonymous: you are dramatically mixing contexts. I spent a couple of years creating a marketing database system, we used no foreign keys. While it is not stated on every single post, it is often stated on the blog that it is crucial to know your context. This blog is almost entirely about business applications. Anybody with two brain cells firing uses foreign keys in biz apps, unless they began programming after about 2001 and confuse the web with programming.KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-73058786100366204462009-02-13T03:58:00.000-05:002009-02-13T03:58:00.000-05:00I know this is a very old post but I felt I needed...I know this is a very old post but I felt I needed to comment on it.<BR/>You state that Foreign Keys should be used and they don't hurt performance.<BR/><BR/>This might be true if you exclude scalability from the "performance" factor.<BR/><BR/>What if instead of a database of students I have a database I don't know, with all the citizens in a country(Let's say, China) plus their 'tax-payment information'?<BR/><BR/>A single database wouldn't be enough in this case, the usual solution is to partition the database ... anything ringing yet?<BR/>Do you get why GoogleAppEngine's database doesn't allow relations in their database? Besides, CPU consumption when joining tables, goes really high and such...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-62992111555579988772008-01-30T15:36:00.000-05:002008-01-30T15:36:00.000-05:00Fernando,I would suggest looking into using Androm...Fernando,<BR/><BR/>I would suggest looking into using <A HREF="http://www.andromeda-project.org" REL="nofollow">Andromeda</A><BR/> as what you are trying to do is something extremely similar to a basic form of the current project that I am working on.<BR/><BR/>Andromeda work off all of the information Ken shares in his blog, and he is the original creator of Andromeda. So I would really suggest taking a look at it.Donald J Organ IVhttps://www.blogger.com/profile/14412453157119493777noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-9076839719487617192008-01-26T15:44:00.000-05:002008-01-26T15:44:00.000-05:00Thanks Ken,It seems it's not as simple as I'd orig...Thanks Ken,<BR/><BR/>It seems it's not as simple as I'd originally thought. But on the other hand it wouldn't be so difficult for someone moderately versed in DB design.<BR/>I'm going to re-read your whole series of weekly posts to refresh some concepts.<BR/>I also believe that (the inevitable) ongoing changes to the DB will break other layers and they will have to be regenerated.<BR/><BR/>Anyway, I'll give it a try.<BR/><BR/>Thanks again,<BR/>FernandoAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-83523774235286490802008-01-25T19:27:00.000-05:002008-01-25T19:27:00.000-05:00Fernando, that is a lot of questions, but I'll see...Fernando, that is a lot of questions, but I'll see what I can do, and hopefully some other readers will jump in as well.<BR/><BR/>Item 1: The "Amount of Ingredient A" and "Amount of Ingredient B" look like a violation of first normal form. The ingredients should be out in a separate table. <BR/><BR/>Item 2: The "Number of Molds" may also be a calculated value that does not belong in the table. Or at very least you may need a list of molds per item.<BR/><BR/>Item 3: "As you can infer, once defined, this table remains immutable." All tables are mutable forever. Never be afraid to add or remove columns as your understanding of the situation increases.<BR/><BR/>Item 4: "Then, the objective is to keep daily records of the fabricated products, as well as sales. For this, I have thought of adding two more tables" This is the right idea. You will do very well to put a row in a table every time somebody sneezes. Think of these highly detailed tables as the foundation upon which you can build anything. But if you leave out those details then when somebody asks for them you have say, "oh, we're not recording that, sorry." As for row counts, you will have no worries with the kind of row counts you are talking about.<BR/><BR/>Item 5: "everytime I ask the RDBMS for the # of units of any given product currently in the inventory, will it have to scan thousands of records...." This is where most people denormalize, including myself. You can have a column in the items table for "on hand inventory" which is the sum of all manufactures less all sales. Your program must increment and decrement the on-hand inventory whenever items go into the journal. The nice thing is that you can recover from bugs by recalculating from detail. I use database triggers myself to avoid mistakes in application code.<BR/><BR/>Fernando it sounds like you have the right instincts, don't be afraid to follow them. If you are afraid that 10,000 rows will take too long (and I can assure that is nothing), then write a program that inserts 10,000 rows into a table and then test the query time to get a SUM out of that table. You are on the right track, keep it up and report back!KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-67304160059581100372008-01-25T18:35:00.000-05:002008-01-25T18:35:00.000-05:00Hello,I'm following with great interest your weekl...Hello,<BR/><BR/>I'm following with great interest your weekly installments on DataBase design, but, as is always the case, every time I have to apply the concepts in real-world scenarios I'm clueless as to where to start. It seems I'll be an eternal beginner in the vast ocean of software development...but I digress. Case in point, could you please give me a hint as to how to tackle the following scenario?<BR/><BR/>A company manufactures 150 distinct products. Now, in the spirit of spelling out the data, I think this merits a table (e.g. Products) listing the 150 items, with Columns such as:<BR/>- ProductID (PK)<BR/>- Description<BR/>- Width<BR/>- Height<BR/>- Weight<BR/>- Amount of ingredient A<BR/>- Amount of ingredient B<BR/>- Number of molds<BR/><BR/>As you can infer, once defined, this table remains immutable.<BR/>Then, the objective is to keep daily records of the fabricated products, as well as sales. For this, I have thought of adding two more tables: One for keeping track of the manufactured products and the other one for keeping track of the sales. Keep in mind that not all 150 products are fabricated in every working day. As a reference, an average of 20 distinct products are fabricated in a single day, and it's in the varying nature of daily production where I'm stuck. I don't have the slightest idea on how to spell out these tables for keeping a journal of IN's (production) and OUT's (sales). For example, if I add one record per day in the "ManufacturedProducts" table, then I'll have something like 300 records in the first year (discounting Sundays and public holidays), 600 records in the second year...and so on. On the other hand, due to the granularity of sales, the "Sales" table (or whatever name it will ultimately be given) will have several records per day, each representing sort of a "log entry" that's added every time a product or group of products is removed from the inventory. Now, I don't think this scales well and I'm also not sure if it makes sense at all. For example, in the 5th year, everytime I ask the RDBMS for the # of units of any given product currently in the inventory, will it have to scan thousands of records in the tables and do the corresponding math to return the integer value I'm interested in? It seems pretty expensive to me. There must be a more efficient way of doing it. Obviously my reasoning must be (very) flawed.<BR/>I'm not implying that this case of simple inventory tracking is a difficult one, but it just happens that I don't (yet) have the necessary skills to do a proper modelling of the data. <BR/><BR/>In a few words, could you please tell me what the DB design pattern is for a simple case of inventory tracking like this one?<BR/>Thank you for reading and thank you for your help in advance.<BR/><BR/>FernandoAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-4491737334261841642008-01-21T09:42:00.000-05:002008-01-21T09:42:00.000-05:00Anonymous: I can tell you what happens if you use ...Anonymous: I can tell you what happens if you use integer keys throughout: you take twice as long to write an app that performs half as well and is more error prone. All in the name of saving yourself from edge cases.<BR/><BR/>That being said, the edge cases must still be addressed:<BR/><BR/>1) A discontinued classroom (or terminated teacher, or discontinued course) is an example of a parent row you cannot use anymore. The kind of key you picked does not affect how you enforce that. There are different ways to answer this which will come up in a later entry.<BR/><BR/>2) Renaming courses or teachers does not require their codes to change, any more than it would require a meaningless and useless integer key to change.<BR/><BR/>3) I don't know what you mean by a changing period.<BR/><BR/>In short, these are edge cases that are distracting but not informative, and whose solutions are not affected by the kind of keys you choose. They should not play into the definitions of keys.KenDownshttps://www.blogger.com/profile/11117175783163937575noreply@blogger.comtag:blogger.com,1999:blog-426922399870577072.post-20181926240934538042008-01-21T00:03:00.000-05:002008-01-21T00:03:00.000-05:00So what happens when a classroom is renamed or dem...So what happens when a classroom is renamed or demolished? A period changes? A course is renamed? A teacher marries and changes surname?<BR/><BR/>A whole bunch of error prone manual update statements that could be avoided using integer primary keys?Anonymousnoreply@blogger.com