Another Design Question 3

[AbidingDude]

Hello, I'm trying to set up a database for the shop I work in. I want to start tracking defects. Currently, when we have a defective part, we have to fill out a paper defect tag and keep it with the part. I'd like to get all this info in a file.

There are two aspects to the defect tag - the problem, and the resolution. I'm somewhat new to databases, but just to get started, I have a database with 1 table. It has fields for the defect info and the resolution info (who ok'ed it, what was done, etc.) I have an autofield as my primary key to guarantee a unique tag number. However, I'm thinking I should have the resolution in a separate table, but I'd like the tag number to be linked to both the defect and resolution tables. Can that be done? Or should I just keep it as one table?

 

[Olaf Doschke]

That's a good design problem to discuss.

The temporal nature of this means you'd start only filling in the defect info section of the record and then later fill in the resolution. There is a technical need of setting the resolution section to some defaults, most probably NULL.

This already gets you in the area of personal taste about databases. Some people tend to avoid any NULLable fields, they point out the need to put this data into a separate table. I work with NULL or 0 in my case in a table recording ingredients of products and their target and actual weight. Target weight is their formula and the actual weight later comes from an electronic scale. The big advantage is as you already said yourself, since both data is in the same record you can't fail to relate the target and actual data or in your case the defect and resolution data.

Since there always will be a resolution - and if it is passing the defect parts to waste management, nothing speaks for creating two tables. Technically you could, everything you need to make this a so called 1:1 or 1:0-1 relationship (in case there will be no resolution, even not the waste management data) you have the possibility to turn a relation you normally do for a 1:n relationship into a unique key at the same time, and thus only allow at max 1 related record to the main defect record. That's how you technically do this in relational databases, the resolutions foreign key is its primary key at the same time.

What speaks for a separation of the data is what other data you collect surrounding this, because of course having all defect and resoltion data in one table you have one primary key (your tag) for the whole record, so the same tag id can refer to both the defect and the resolution. Think about something, which would need to distinguish between referring to the defact or the resolution, nothing like that comes to minf. Typical related data would only refer to one or the other exclusively, so the meaning of the reference meaning the defact or meaning the resolution would be clear fromt the context, for example data about the reapir time certainly is about time needed for the resolution, it's clear this time was not needed to casue the defact.

This thought also has to stand for references from the whole record to somtheing else. In my case an ingredient of the formula does not only have the target and actual weight attibutes, it also points to the vendor of that ingredient. From the context it is clear that the vendor refers to the used iingredient, to the actual weight of the component and not to the target, but that is not defined technically, that is knowledge about the meaning of the data. You could also imagine the vendor reference already is given before weighhing in the formula ingredient, to only take a certain brand for your cookies choclate chips and not any choclate chips.

I prefer the big advantage of having all related data in one record, even if there is further detail data referring to one of the records part only, this typically is clear from the context. That said, you can normally of course rely on foreign keys not to point to wrong parent data, we all rely on keys to be valid data and not point to wrong records, these joints of the data are not unreliable failing points of it. Even considering someone wanting to harm your data, the data itself could be deleted or changed, having data in the same or in two records does not make it any more or less safe data, for that matter you do backups proving how data was before the hacking of it, so such thoughts should not drive your database design.

Concluding: I would recommend putting all data about the case of the defact and its resolution into one record here. Only argument against it would be you collect very many attribures in one row, and another argument against such a large record would be any repeating parts of it belonging into 1:n related detail data, defect types needing different data would also speak for having separate tables. But it's not the nature of the resolution to be added later, that makes a second table a must have.

Bye, Olaf.

 

[Mike Lewis]

I think the answer partly depends on the likely ratio of resolutions to defects. If most or all defects get resolved, that would be an argument in favour of a single table, with each row containing all the information for one defect, including its resolution. This would help keep queries and updates simple.

But if there were many defects that never have a resolution, that would be a case for two tables, with a one-to-one relationship between them. The defect table would have a foreign key pointing to the primary key of the resolution table, and vice versa (the FK from defect to resolution would be NULL of the defect is unresolved). The advantage would be that the resolution table would be smaller and therefore more efficient to access.

I am assuming, of course, that a given defect can never have more than one resolution, and that a resolution would never apply to more than one defect.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, since you can only have finite defect parts of anything stored in a workshop, there should at least be the resolution to let a defect part go into waste management, recycling or disposal of hazardous waste, if that applies. So in the end there will be some resolution related data.

In MSSQL with its pages of data a NULLable field really does not consume disc space while it's null and even if some fields have default values needing bytes you just reserve the space you need in the end anyway.

I would still recommend the one table with all I know about it and summarize the decision is not only about all the attributes belonginng to one entity, but also about how other entities are relating to what group of attributes, so the decision should neither be about belonging to a certain entity=belonging to a certain table, but also about how other entities relate to the data.

In my other more familar to me case, ingredients surely belong to the formula, but have their separate table simply because of multiple ingredients belonging to one formula, their number is one very strong reason for the separation, but also because each single ingredient might have a separate and different vendor etc. Not only do ingredients have their own group of attributes, the also relate to multiple other entities and vice versa.

There is no strict rule of what to separate nor of what to keep together, every single attribute may become important enough to justfy an own table for it or simple enough to put as an attribute of a table you already have.

Deciding for one table, that table should rather be called repair jobs or cases, not defects. Once you call it defects the solution related attributes become data you don't want within the same table, because they are not direct attributes of the defect, they are defect related, like a child or sibling entity (another table) is and that would speak for a separate table.

I'd say just the case of no solution ever would justify a 1:0-1 relation to a solutions table with no related record to begin with, only getting a 1:1 related record when the solution begins, i.e. a repair is scheduled, for example, or a spaare part is ordered. But you could also have the first null fields set to some values. The spare part order could be related to solutions table or to the repairjobs table. It's clear you order a spare part for the repair, you don't order a defect part, that's what I mean about the context, the data does not need the separation to be interpreted correctly, you also don't have two repair finishing dates, everything you have just has one value for the repairjob.

Once you find somthing, which you have multiple of or which might relate to the defect or the solution and you couldn't distinguish it, that would make a separation necessary so you have two different foreign keys pointing to either defects or solutions. Since a foreign key is defined not only by being the primary key of another table but also of which other table, you can have a tag id related to defects and to solutions and they'd be distinguishable.

I could also judge from the point of view on another topic, again about the products database I worked on 17 years. There is QA about products and there is a list of defined QA tests to do with products. I inherited the database 17 years ago having a table per such QA test with test specific columns for certain measurements at certain deadlines past the production date. We split all these very test specific tables into a few tables about what criteria were measured or judged on whioch deadlines and on which samples. This unified the whole QA tests and split the very test specific data into more atomic informations, as you can then bundle that atomic smller single criteria tests to further and other macroscopic tests, the data became far less rigid and you could even have statistics and developments of single criteria measured in different tests, you couldn't only compare results of the same tests, you can noe compare results of the same measurements within tests combining very different groups of test criteria. You can have very individual test plans and yet very informative statistics even cross product type borders, as long as products have common attributes.

What we talk about here in case of the solutions are just head data like who gave the final OK of the repair, nothing differing and needing more or less data to justify one or more separate solution related tables. You may of course have data about the single steps of a repair, but you collect data about the whole repair case in one record halding info about the defect and the repair, I see no need to split that head data. In a car workshop you will have several work items related to a solution, but there is no need to split the solution head data from the defect head data, it's both about the same repair job and it's clear the work items are about the solution and not about creating the defect like the spare part order is not a defect part order.

Bye, Olaf.

 

[AbidingDude]

Well thanks both for the input! I think I'll go with one table for simplicity. I have the defect fields set to require input and the resolution fields set to optional.

I just recently learned how to set up a form (using OpenOffice). I have the defect and resolution fields set as type MEMO, so I figure I can just maybe timestamp and append any extra info if it comes in into those fields.

 

[Olaf Doschke]

I don't know a database enabling to set a field to "optional", though it sounds correct, that's not very typical database terminology.

Using OpenOffice Forms, what is the backend? An internal Open Office database (OOo Base)? Or do you access data via JDBC/ODBC or ADO?

Bye, Olaf.

 

[AbidingDude]

I just meant "optional" in the sense that I have the "Entry required" option set to "No".

The place I work in is not very tech-savvy. Even having computers is fairly new. Right now I'm just using a form to directly update the file.

Although, I am trying to learn how to interact with databases indirectly. There's talk of getting a little intranet going. I've done some CGI coding before. I'd like to eventually be able to have the guys on the floor fill out an HTML form that can update the database... But first I have to learn databases better.

 

[Mike Lewis]

I'd like to eventually be able to have the guys on the floor fill out an HTML form that can update the database.

That's certainly possible. In fact, it's quite a common thing to do.

Strictly speaking, it's not the form that is updating the database. The form will pass the entered data to some sort of script running on the server, and that script will update the database. Personally, I use PHP and MySQL for this sort of work, but there are many options available.

One thing you must take into account is the need for validatation. Your script must check, for example, that numeric and date values are withing an acceptable range, and this must be done before the data hits the database. Up to a point, you can do that in the web browser, via Javascript, but you will mainly do it in your sever-side script (keeping in mind that a knowledgeable user can easily circumvent the Javascript checks).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Since you use Open Office there is no need to use HTML and CGI or PHP, you can bind to data directly, but centralizing data and not having it as part of a document only will rather need a "real" backend you access via ODBC/JDBC. I am actually not sure, whether Open Office Base is like access. Then it will be possible to be used detached from the form itself and give you a pure data file as your backend to a form/document frontend.

I'm fine with that, as soon as you have an intranet web technology is the better technical choice for being able to move into internet or "the cloud" later, but a solution equal to Access is okay to start with such simple data entry forms and already have data in a reasonable format to later upgrade into a database server.

PHP and MySQL is an entry level choice, too, but Open Office or Access are comparable and simpler ways of starting a database application, the PHP&MySQL hurdle is having to handle HTML, Javascript, the web server and the Sever&Client technology, too, just for having a simple form, while Office, no matter if Open Office or MS Office, has everything in one place.

Bye, Olaf.

 

[Olaf Doschke]

>"Entry required" option set to "No".
Well, database terminology for table columns you need to enter vs you not need to enter is speaking of nullable vs non nullable columns, where NULL is really just saying "no data entered". You can also have columns with a default blank or 0.00 value and also call that "Entry not required", but NULL really stands for no value.

As you said yourself, you are novice to the whole topic of databases yourself. Just note, that no database will stay at one table, such a database rather is a list, not really a database. But it's good enough to manage a list of repair jobs and already much better than just paperwork.

Just googling for a car workshop erm with repairjobs I find this:

http://www.texample.net/tikz/examples/entity-relationship-diagram/

Don't take this as your immediate goal to have that, but it shows a typical database will have more data about employees, clients and cars (or whatever your shop repairs), even if that rather is about things like fuses, lights and other inventory of offices needing maintenance and repair.

It pays to have such a diagram, once you have more than one entity. This page rather is about the tool used to create such a diagram, disregard that, you can also do this with pen&paper to start with, but I surely encourage you to think about having more than merely one table of repairjobs. You will want to know which employee uses your form, for example and who entered what repairjobs or OK'ed the repair.

Bye, Olaf.

 

[AbidingDude]

Strictly speaking, it's not the form that is updating the database. The form will pass the entered data to some sort of script running on the server, and that script will update the database. Personally, I use PHP and MySQL for this sort of work, but there are many options available.

Gotcha. So it sounds like something like MySQL is needed for this kind of access. I can't just write a CGI to poke data into the database?

 

[Olaf Doschke]

CGI is not naming the programming language to use. PHP can be used in CGI mode, though others are faster. CGIU can also mean using perl or other languages, you have to see, whether that supports accessing MySQL. But other CGI languages support other databases. So what did you program so far using CGI. CGI itself is just "commong gateway inteface" - naming the part of the client/server http technology that is used as the bridge between the webserever getting a request and whatever concrete language executed by it. In itself CGI doesn't define any programming language or database, it really just names a general interface between the webserver getting a request and forwarding the parameters it got to a cgi language. The most prominent part is having a CGI folder in your webserver, which has your chi scripts. In remote and far away times these typically where perl scripts in linux/apache web server and asp (classic) scripts on IIS web server.

The fastest mode of PHP today is not using the classic cgi module, but mod_php and IIS also doesn't use the cgi-bin folder for any recent server side scripting language, but has direect associations of request file extensions to the interpreter called.

I would not say you need to go that route at all, if you just need this for your shop you can use the OO solution you started as good as an Access solution. You just need to think about scalability. If you stay fully in the OO world you will use a database not commonly used in the web, like you can host Access mdb as data on special hosters, you may also hos OOo Base databases, but the common hosted database is MySQL. To start with, you can use OO ODBC connections to MySQL and use OO forms, but that frontend also isn't the norm of browser driven sites. I don't know how good you are in html and css and javascript, but your cgi capabilities are surely not, what you can use today as the norm. cgi is a legacy technology for legacy languages.

Bye, Olaf.

 

[Mike Lewis]

So it sounds like something like MySQL is needed for this kind of access. I can't just write a CGI to poke data into the database?

MySQL is a database. It is one of many such databases that you could use to store and retrieve your data. Other examples are Microsft SQL Server and Oracle. But MySQL is a good entry-level option, not least because it is free and fairly easy to administer (many web-hosting packages have it pre-installed).

As well as the database, you will need some form of programming or scripting tool to actually update the database and to retrieve data from it. Olaf explained how CGI fits into that picture (and see also his remarks re Open Office). I mentioned PHP because that's also a good entry-level option (again, it is free, and is often pre-installed with hosting packages).

I hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AbidingDude]

I hope this makes sense.

It does... Mostly Not to get too off-topic, but just as some quick background info, even though my label for this forum says "Programmer", I feel like that just barely applies. I'm mostly self-taught with programming. I had one programming class in college ("C for Engineers"), the rest I've tried to learn on my own. So if there are seemingly weird knowledge gaps with me, that could explain it.

So because I'm most familiar with the C language, that's what I've been using to write my form handlers, and then learning HTML and CSS to put the pages together. Now I'm looking to have a program write to the database, either with C or maybe I could learn PHP.

 

[Olaf Doschke]

Well, PHP is a natural choice, as PHP is tightly coupled to MySQL, PHP has many MySQL related functions. It is not vice versa though, MySQL does not depend on PHP. And also PHP has matured to have a much more general database access layer called pdo (php data objects) to work with most any database.

But as MySQL is a hosting option most hosters offer, so is PHP coupled with that. Writing server side code in C would require to write DLLs you can use in isapi compliant web servers, it's not a natural language for web purposes though there are C++ libs for mysql which are also the base for PHPs mysql functionalities, PHP is written in C++, just as very many of todays 4th generation languages. I wouldn't work with C in the web, though. If, then the best platform for that would probably be node.js, which by its name is a javascript platform, but a) server side and b) allowing add ons written in C/C++ - see

https://nodejs.org/api/addons.html

So you could write a node.js add-on in c++, but that'll not be the endpoint. You don't get around learning more and other languages today, C/C++ rather is limited for desktop development and base libraries to use in a larger context. And if you now think: hooray, I can use C/C++ mostly and a bit of js in node.js, I fear you overestimate how much of your coding can be in such a C/C++ library and how much overhead it is alone to write this module to be usable within node.js - Writing an add-on means implementing a interface definition, which is not very simple. On top of that, I don't even can tell and teach you node.js, this is only my judgment from a glimpse on this and some other findings.

It will be much simpler to learn enough PHP to handle html forms than to learn everything you need to know to embed C/C++ libraries in something web related. In about 1999 ebay in germany (called alando then and only later bought by ebay) was using a C/C++ isapi dll, that was the last time I saw something like that. So that was over 17 years ago.

Bye, Olaf.

 

[AbidingDude]

So, do you recommend MySQL over OpenOffice Base? I've been trying to work with Base because I already have OpenOffice installed and use most of the other components (Calc, Writer, etc.), but if MySQL is easier to implement, it sounds like that might be a direction worth going in.

 

[Mike Lewis]

Why not try it for yourself? Try installing it (if it is not pre-installed). Then find a tutorial and work through some of the examples. That way, you'll get a good idea of whether it is the right solution for you or just a big hassle.

Personally, I tend to use MySQL (and PHP) whenever I have the choice, but that reflects my own experience, knowledge and prejudices. Yours might well be different.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

An Office solution, no matter if MS Office or Open Office, will always rather stay an Office solution, limited to local use.

I already mentioned there are hosters also hosting Access DBs, but this is seldom. I don't know hosters hosting OO Base. You might find someone, but having Data online is only one component. The Access hosters I know offer creating a DSN with which you could access the data from asp classic VB and today perhaps also PHP, but the web doesn't let you use a document frontend like Word or OO Documents, what transitions to web and makes your whole code movable to the bigger scope of access from everywhere is using a HTML interface.

But do you really need to go online rightaway? The LAN cannot only host a web server for an intranet, once you have a (domain) server - which could be a linux machine with samba as the software to install for LAN access with windows clients - you can also host the database only to start with. A MySQL Server does not need to be bundled with a web server and a server side scripting language like PHP, you can simply install MySQL on a small business server or Linux server and use that as backend for your OO Document frontend in the first place, don't make the overall solution a construction site and start from scratch. When you have your data in a MySQL server you can build the HTML frontend as second step, and if your OO document forms are mainly html forms that helps to make that transition easier, but you have to combine HTML with PHP or something alike to make that a web form. No need to start with it before you have your data in MySQL, though.

Notice the more public you go the more security concerns play a role. You can always fear security breaches, as also your local computers are internet connected, but with hosting you have at least one more company you need to trust: the hosting company. And in second level all their customers - they know how to get into their hosting space, they can easily try to get into "neighbor" accounts of their "landlord". Besides that, the big cloud companies Microsoft, Amazon, Google and Apple are surely always under attack, you can bet on that. They do care for that aspect with appropriate measures and in the end you can be safe there, but you can never really tell and have any traces in your own hands, whether anyone already stole your data - copying happens non destructive. Ransomware is a contrary concept bribing you to pay for a decryption of your data. In such destructive cases backups are the best solution against harm, but the silent and non destructive hacks are the ones you or your customers can also be victims of without anyone ever noticing, until credit card info is used. That aspect of payment data alone also speaks for making use of services like paypal, which keeps you off the task to store such sensitive data secure for your customers.

I would always only go online with just the data I need to put online for end users and customers, there is no other choice about that. Even there, let's look at the most typical order data you then will have online for the convenience of your customers to see order status and past orders, I would never just connect to that onnline data as the main order database but pull sync this to a local database server, which makes a hack of your online database a low impact.

The only other thing I put online are encrypted backups. Here putting that online is no risc because there is strong encryption, also nothing else saves your data not only from local disc drive failures, viruses or ransomware, but also from fire, water, theft or any other non technical harms. You can also use the traditional backup on tapes - todays rather blu rays perhaps - and take one copy home and leave one in the office, but online backups offer much more secure storage of your data for the desaster recovery case than anything else.

Bye, Olaf.

 

[AbidingDude]

So, as I've been doing more research, I've been toying with the idea of skipping a database application altogether and going with XML. I just learned about XPath and XQuery, and the XML format is easy to write to. Plus I'm not anticipating millions of entries. I'm wondering if that might be a more efficient way to go.

 

[Mike Lewis]

XML isn't really a substitute for a database. It is a way of storing data - one that's especially good for transferring data between different applications and platforms. But you can't run a query against an XML file (well, you probably could at a pinch, but it would be horribly inefficient). Nor can you easily update an XML file other than by completely rewriting it.

In the context that you described at the start of this thread (tracking defects, etc), XML would not be the way to go, in my opinion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads