 Okay, here we are it's Wednesday three o'clock and guess what is the economy in you and guess what that's Chris Leatham This is his show, but I am a reciprocating host today. My name is Jay Fiedel. I like Chris and I like the economy in you and Today we're gonna talk about his favorite thing, which is computer programming. Welcome to your own show Chris Well, thank you Jay. Thank you for having on my own show. You know, one of the things that we talked about was You know, this is think tech right, so it's about technology. Tech is our middle name. Tech is our that's right So if we're gonna talk about technology, you know One of the things that we talk about in what we can be doing to grow our economy is bring in more technology So I thought well, you know, what are the topics of technology is moving away from spreadsheets It's sort of traditional flat file databases that people's often use and I've seen them throughout Yeah, it's the it's the corner of the realm for most businesses. Yes, it's visible right in front of you Uh-huh easy to use relatively speaking. Well, it's WYSIWYG, right? What you see is what you get Yeah Now the thing is the problem with database or spreadsheets as a database is their flat file They don't they're not it sort of run out of efficiency at some point so, you know, you don't get to store you store data in a flat file structure and Eventually what happens is it no longer works efficiently for you Yeah, you got to duplicate the data in various flat files and that's why we're calling this show Relational databases are the best. That's right because we all know we all love our relations Well databases love their one big happy family, yeah, well, it's an imperative So when we talk about database design, you know, a lot of people go well What's the difference like in Microsoft office in the Microsoft office week? There's a program called Microsoft access Microsoft access is a desktop database platform Much like your favorite database program, which is Filemaker actually I get to give you my history on okay I started out with something called debase two and PS. There was no debase one It was a gag Then it was debase three and then that well, I never got the four I mean there was a four then I went to Clipper Clipper with a compiled language instead of an interpretive language This is in the 80s and Clipper really opened it up for me But then I saw ASP net Microsoft and Microsoft use sequel, you know So it's ASP net running the sequel, you know search language and that was really something So I started getting into that and I wrote a lot of programs using that And I'm you know for some years anyway And I'm familiar with access because access is kind of you know, the younger brother if you will of all that stuff with ASP net and sequel And I forget what's another database language in there and the framework thing. Do you remember? No well Within the within today what we have as a language called link which is language integrated query anyway So at the end of my story is For a lot of reasons I got out of that back in the early 90s Maybe the middle 90s and I do remember how powerful it is and I do remember that relational databases were all the rage And I do remember that access was their flagship What do I call it? It's really consumer level because it's you get dragged and dropped you can make it happen That's right drag and drop. That's right, and you've been programming in that So this is very valuable to discuss exactly what is the condition? You know in terms of public acceptance professional acceptance of the Microsoft access program today and ps Microsoft stock has doubled in the past couple of months. So there must be something good happening there Well, the thing is is that when when people started talking about How to sort of track data? The spreadsheet only got you so far and I started working with Microsoft access live well interesting like what while I was living in Indonesia We had started an investment company called Pacific Financial Services And we needed a way to get people their monthly reports Well, we were using Excel spreadsheets initially which meant the girls who were doing the data entry had to enter in all The data for every client for every security they had and it took them days to do this work So by setting up an access database We could plug in the values of the the stocks that they held in the portfolio and generate the reports and it took minutes Instead of hours was made it was an amazing improvement and in the ability for us to get work Accomplished you haven't put in the time to program it because you didn't have any time at all with a spreadsheet It was right there was he would yes except of course then you had to key in all that data over and over and over again Which of course became and it could be different than a second or third or fourth time That's the problem. Yeah, and it wasn't accurate, you know It depended on somebody carefully and inputting it the right way each time each time And so by by developing a database application where we could track the history of its stock price and then Identifying a purchase a sale You know when this purchase the stock was purchased and the stock was sold We could then it was easy for us to figure out how much money the client made And that gains or losses on that stock much much less work And so because we could download that information into the database and then simply apply our clients trade Suck it up from this from these spreadsheet into access Yes, and that's really powerful to be able to do that. Yeah, they have to re-enter it again Yet again. Yeah, and then you remove that redundant data entry process, which is of course One of the things that I still see in government offices today Well, you'll see somebody come in and they'll enter in the data off of a off of a document enter and then turn around Reach over to another computer and rekey in the same data all over again into another system Yeah So these are some of the challenges that we face when we're trying to develop efficient data models and efficient processes So why why a database? So why relational database? Well It allows you to logically relate to ask, but what is a relational thing a relational database is a collection device Don't that allows you to collect logically related data That's what a relational database is So you can store data in two-dimensional tables that look like spreadsheets Except instead of having columns and rows you have records and fields now Now the thing is is is when you have a table, which looks like a spreadsheet the columns in a table have Specific data types. Okay, so one of the key things that you look at is what's the nature of the data? Spreadsheets just look at it is okay. I put something in it's there. Okay, usually numbers numbers But in a relational database You're gonna store data based on its data type simply if it's a way to mean example Well, for example a date, okay a date or a time that would be a date time Data type that field and that data table. Yeah is only for either text or a date or some kind of structured Type for data. Yeah, so you could have text fields which store, you know things like our names You can have text fields also store things like numbers that aren't calculated values such as our social security number It's really just a text field. It's just a text field. It's just a text field, right? Yeah Now you can store numbers you can store all kinds of numbers You can start very large numbers or very small numbers in databases And you can have numbers that are real numbers or integers or or fractional numbers or decimal point numbers currency values all these things sort of fall under the category of number because they're Numbers means that you could perform addition subtraction multiplication and so on so you were getting to the point of trying to Tell me about the relations between the tables, right in a database which has related tables Well, yeah, and then the way that works is you have something called a key So one of the keys that databases use By our government to keep track of us as taxpayers as our social security number or a taxpayer ID number that becomes a key And any data that that correlates or to us as an individual can get stored at separate tables Based on that key. Well, there's a parent and a child here, right? There's a parent. What is the parent? Which one is the child? Well, the primary we called well, you might call it a primary or foreign You have primary or foreign keys, right? Exactly. The parent is the child is the child, right? So when you have for example, when you go to the store and you buy something and then this is sort of a common analogy of why we use relational databases and how stores use databases is that when you go into shop for something and you Have ten items Okay, so you come in you're the the customer and then you have the ten items that you're purchasing Okay, but you also had a transaction so the transaction of purchasing ten items that gets stored in a relational table and You as the individual gets stored into that table as the child or the foreign key in other words Let's say for example your social security number They've got or they've got an identifier number for you in system every time you come into the store and you buy new goods It tracks the transaction so you have the sort of the invoice that yeah, that's just a master here That's the parent the parent with the with the key right and then you have two children You're describing two foreign keys one one is the products, right? The various price and go into the invoice so to speak and the other is me as a customer, right? I have to be linked to the invoice. That's right. So what you have is you have you You have the transaction and then you have the individual items within the transaction each Table has its own unique primary key or for our primary key But the individual for example you as a transaction would be stored in the transaction table But then that transaction also has its unique identifier and usually these numbers in most databases today are sequential So there's a sequential number assigned to that I got a graphically you can see this stuff graphically, right? Yes, you can actually you have to establish it graphically if you're using access They give you these Graphical representations of the tables and all the fields and then you you know physically go from one table to another table And you know draw your mouse to in order in order to make a line between the one and the other So to connect them up well when you're designing data tables when you're designing now Here's the thing about when you when you talk right and the reason that this is an imperative is because When you build a database you're building it based on your business model However, it is that you do business Okay, so for example if you are a company that makes surfboards Okay, town and country surf is an example of a company that makes surfboards Okay, there's certain information that they need to track for example If there's a custom board being designed for a customer, you know for a new customer Surfer who wants specific features these board well all that comes into the order Okay, now what happens is is you have different people working on different parts of that order So as that surfboard moves from one and one part of the factory to another part of the factory You need a way to effectively keep track of who did what how much you're paying him to do the work Because you may have negotiated different rates with different people and then where the board is in the in the Board the the board as it moves through that's the you know building the board parent So to speak right and then the foreign keys the children are all the people out there and the things they're doing So you could have a bunch of children and only the one parent, which is the board moving down the assembly line That's right. That's right. So and then I agree with you It's like different you have to look at the business process in that company in that enterprise Yes in order to understand first you have to understand the relationships Exactly what is happening and that's a mind-blower so much so that we're gonna take a break so we can you know relax our minds But just one second and get off the hot seat over here and then we'll come back will be refreshed That's Chris lethem the economy and you talking about relational data business. They are the best Aloha, this is Reg Baker with business in Hawaii We're a show that broadcasts every Thursday at 2 o'clock We would love to hear from you and you can reach us in several different ways We have a hotline that you can call in at 415 871-2474 Or you can email us at think tech Hawaii comm or you can tweet us at think tech H I Looking forward to hearing from you and seeing you on our next show Aloha Hey everybody my name is David Chang and I am a new host for the show the art of thinking smart I'm really excited to be able to share with you how to get the smart edge in life We're gonna have awesome guests in the military business political nonprofit world So no matter what background you're from we have something for you Please join us every other Thursday at 10 a.m. At think tech Hawaii comm or on the art of thinking smart comm I look forward to seeing you Aloha everyone, I'm Maria Mera and I'm here to invite you to my bilingual so Viva Hawaii every other Monday at 3 p.m We are here to show you news issues and events local and around the world join me This is Steve Katz I'm a marriage and family therapist and I do shrink wrap which is now going to every other Week all during the summer and maybe forever after take care of your mental health this summer Have a good time do what's fun and take good care of yourself. Bye. Bye Aloha I'm Kirsten Baumgart Turner host of sustainable Hawaii. Thanks for watching think tech this summer We have a lot of terrific shows of great importance And I hope you'll watch my show too every Tuesday at noon as we address sustainability issues for Hawaii They're really pertinent as the World Conservation Congress approaches in September and the World Youth Congress that's focusing on sustainability Next year as well. Have a great summer and tune in at noon every Tuesday Okay, so when I do this it means that I don't have when I do relational tables It means that I don't have to enter the data, you know multiple times. That's right Parent will find it in the child table. That's right. That's right. So when you want to generate so Why go through all this headache of designing a database in the first place? So you have your primary key and you have your foreign keys. Why is this the thing is this? Why do people if you're in business? Why do you want to build a database to track all this data? Well, if you're the ABC store for example And you want to know which products are selling which products are selling at what price point? So if you have different prices going on in different stores, you know at what price point you're going to get the most sales So you're able to track all that information in your database and writing reports against your data is one of the key Benefits of using a database platform. So if you have relational tables Yes, then the language that you use to extract that information to make those reports to answer all your questions Would be in the Microsoft world. It would be sequel sequel structured query language Yeah, now that really is powerful stuff That's been around a long time and it's the coin of the realm isn't it it is and you know Here's the thing when people get into databases, they think that databases are really complicated and they're not You know, there's only four four commands with working with the data They're either select update insert, which is inserting a new record or delete There's just four commands. It's not that hard to learn One of the reasons I was compelled to do this show is I think a lot of people they look at databases It's from going from Excel to a database and they get sort of There's a sort of terror that runs through them that oh my god I've got to learn this thing that's really really difficult and it's and it's just not so You know, you know, it sounds like it's like one of these things where years ago, maybe 20 30 years People were terrified of spreadsheets. Yes Make a formula on a spreadsheet. Are you kidding me? Yeah, okay, but now over that period of time We have new generations. We have new requirements put on people in companies. You know, you got to do this This is part of the job, you know, and I suggest that You know, although you got to say that spreadsheets are much more complex more powerful more sophisticated than they were Even have macros that sound a lot like program language. Yes, but but a relational database That's where the action is and so maybe it won't be right now, but it'll be soon ten years Maybe everybody will have to know about these things and the programs will get more powerful and this brings me to the competition for for Debates to for Clipper for ASP net and ultimately for your your precious access You're a Microsoft access program. Yeah, and what is that competition? There's two programs out there that are drag-and-drop. I've looked at both one is quick base quick base is is a is a subsidiary of the Intuit company and It's a drag-and-drop database. You can make relational tables and all that and the other one is my favorite the one I picked ultimately which is file maker. Okay. Now. Tell me why those programs aren't the future well, I think there they just as well could be the future just like I think Accesses is used ubiquitously for people who understand and use that desktop databases now The challenge that you have with any of these desktop database platforms is today. We want things web enabled So you bet yeah now quick basis is software as a service program. It lives on the web Yeah, you don't have to have anything but a browser to use that program pretty interesting Can't do that with access actually you can today if you have a SharePoint server You can use access have to have a server you have to have a SharePoint in the case of quick base They have the server. Yes to worry about that's right And it's all in the cloud Yeah So the the beauty of of course when you're working with database applications where you have a drag-and-drop environment It's very easy to get your data connect connected up and working But now you have to be a little bit careful when you start working with databases because you can set up your hierarchies or your relationships incorrectly which could Create problems for you down the road So it's always good if you have somebody that you can go to to have and take a look at your database design and see if it Actually works consistent with your business. Yeah, you got to look at what you're doing in the business That's right It takes it really does take some skill and some what you call it business perception Yeah to walk into a business and figure out exactly. What are they doing here? What what how does this table relate to that table? This is not so easy, but you know, I want to add this that I have seen I'm not using it But I have seen programs that actually help you figure it out And that's where this is all going programs that make the relations for you. Yes. Yes Well, there's you know when you work with relational databases and you're looking at your business model It's really great to have somebody sit down with you who maybe understands Databases because databases can go in a relational databases can also use hierarchical components for example and the For example, you may have a table that is exclusive like an animal table And then there may be other tables that sort of are sort of higher up the food chain such as a dog a cat or And so on okay So you can have hierarchical structures within a relational database and sometimes those are important components to establish in your database Because it allows you to manage data more efficiently, especially when you start dealing with millions and millions of records Millions is no big deal now right Millions of records, you know guy walks in he says this is gonna involve millions of records. Yeah, right sure Well the benefit of having computers that have more memory and more power Are more poor of more powerful processors that is that they can manage millions of records easily Instantly and the search capability and the sort capability mind-blowing yeah current databases They're much they're much better And so when you're gonna work with databases when you're when you're ready to get it started The great thing is there are websites like Linda.com L. Y. L Y NDA moment for a shout-out to Linda L-Y-N-D-A.com that is like, you know software University anything you want to learn you can learn on Linda It's amazing, but it's not the only one if there are others to you know There was a time when it was only Linda and Linda was and there is a Linda there's a real person There was a time when Linda was alone and you couldn't find anybody even compare to compare But now it's a lot of people who emulate Linda Well, there's a company called plural site for example, which is because I'm a Microsoft developer and I developed in SQL server and and dot-net There is there a company that provides all kinds of training and there's quite a bit of use stuff available to on YouTube now So that people don't necessarily feel like they're they're going down a slippery slope here learning databases Is something that you can learn Without having to learn all the other programming languages because An access database for example will create the forms for you That you can use to enter in your data now The thing is when you start working with a database you usually have an interface That does not look like a spreadsheet for entering in the information You usually have some sort of a form that has fields and drop-down controls and most of us today have used web-based forms To enter in data, you know But there's a contact form request information or you're entering information for credit card processing or a E-commerce solution and you get that with with when you're working with a program like access or file maker pro Yeah, I mean it's it's forms. Yes, you make a form. It has the fields on it You fill in the fields that data goes into the table. Yes, and now you have Some question you want to ask about what's what's in that table right related to other tables So you now you have a search a search form right or some way of generating a report And so there's not a lot of you know graphic images here. It's a form in It's maybe you look at the table if you're curious how it actually looks it looks like a spreadsheet actually Yeah, okay, and then a form out So, you know, it's not that much to it. It's just that the relations are the other center More than and the sequel commands right to find things well And also one of the great things about using forms is is let's not let's not forget that you have drop-down lists That you pick from so you don't have to key all the information by hand so exam for example Yeah, that's right. You don't pick a state, you know a range of numbers You know something like that so they were validation techniques Well, yes, I want to put a phone number in it's got to be seven or ten digits It can't be 27 digits. It's gonna it's gonna argue and complain with you if you try that that's right That's right, and that and that's of course again goes back to having a platform That's more powerful than a spreadsheet because of the built-in error checking capability of the program when you're inputting the information And you can make that as sophisticated or as simple as as necessary But the value of all of this is that we are moving away And we need to move away from just trying to use spreadsheets to solve In business in business and I remember the valuable thing about access anyway My recollection of it is that any yonkel if he put his mind on it could develop an access program Drag-and-drop is our mental won't go 20 years ago drag-and-drop And and do these reports and learn a lot about his business and he can do it without a lot of equipment All he needed was to download and otherwise have available the access and I think the future It's all gonna be software as a service. It's all gonna be because because the public doesn't want to be involved with servers Or downloading they want it right there And I think all these companies are gonna be migrating to software as a service And that means by the way that it's on the web. Yeah, all you do is call up a browser, you know, and you're you're working That's what's gonna happen. Don't you think and and as a result. It's hard for a given say employee a financial organization To say I don't want to do it. It's too easy. Yeah, he's got to do it Yeah, yeah, and here's the other the other thing now This is by putting things into a web or into a an environment where you have multiple users using your program Or contributing to a program is if one programmer or one person leaves Okay, that doesn't mean that all the programs that he might have developed while you while he was working there You know, there's there's somebody else there who is familiar with what's already been developed Well, that's the problem in development. Yeah, you've got to make it bulletproof. I'm sure this is big part of your work Yeah, you got to make it bulletproof. So when one guy's leaves the other guy can just sit in his chair and it's gonna be Understandable. It's not gonna break. It's gonna be documented. So he knows what it's doing This is really the challenge and this is the value added you can provide Well, this is one of the problems I've had Historically coming in to for example when I worked for the University of California in San Francisco Is there was somebody had been working on on programs privately by himself writing code for eight years and there was no documentation in the code So I asked the guy, eight years of code. I said, well, where's your documentation? He said my code is my documentation No, no, no, that doesn't Well, it was job security for me for a while. I'm sure it was and it would have been for him, too Yeah, yeah. Well, yeah, so So what are the imperatives is if you're going to design a database that you you work, you know And here's one of the the key things about designing databases and let's owners take back ownership of their company Yes, yeah, when people are doing things on spreadsheets and as an owner of a company, I don't know what they're doing Okay, and I don't understand what they're doing. I so you sort of get hijacked, you know That employee now sort of has you right she has you by the spreadsheet Yes, she does she has you by the spreadsheet and so, you know One of the great things about databases is what you set up a database and it's something that's being used throughout the company You sort of take back ownership of your business So there's real value to in having databases that are designed to manage the process of your company Let me let me add this though, which I have found true in every circumstance And that is when you get about the business of building a common Piece of software for a company whether it be a website that reaches into exactly what you do I mean in terms of function in terms of your business function, right or building a database Which kind of looks at the same thing but in much greater detail and much greater power much greater Capability for calculation, you know, yeah, you are actually evaluating your company from a fresh point of view You're rationalizing the thought that goes into what people do in the workflow and all that and ultimately when you build it You're changing the company because now you have learned some stuff that you wind up reapplying back By developing the software, you know as somebody developed software for 20 years for for a variety of companies Is that it's interesting when you start to build out an application? That there are people in the company that will tell you that such and such a thing is true And then in the management level and then you go talk to the people are doing the actual work And they'll tell you it's not always true And then you start to bear it out to all the the logic or the lack of logic or Miscommunication that goes on within an organization. So part of the work of a good programmer is to be an investigator That is the imperative off to people that is the find out find out and we are gonna find out We're gonna find this out over time We're gonna learn so much about this and we're gonna share it with you Chris and me Chris Lethem the economy and you big part of the economy as it should be here on think tech. We'll be back soon Thanks for watching. Say something nice. See you next week folks and they won't stay Stay tuned for more on software. I knew we'd say that. Yeah