What is the correct or best approach in editing a table? 5

[Mandy_crw]

Hi everyone...I have one main table that contains main record that contains name surname and idnum... i have a another table that contains transactions of each of the record in the main table. My question is, what if i needed to correct some of the entries in the transaction table of a particular idnum?

main table
0001 Maria
0002 Mamang
0003 Makina
0004 Mandy

transaction
0001 560.00
0001 340.00
0004 120.00
0001 350.00
0003 450.00

in the transaction table i needed to correct 340.00 to 890.00 and 350 to 670.00... cosidering i have thousand of transaction in the table ... what is the best approach? and if ever ill be using update, please show me how... Thanks in advanced....

 

[mjcmkrsr]

Hi Mandy,

If you occasionally have to update the transaction table you could use a filtered browse e.g.

use transaction table
browse for transactionid = "0001"

and do the required updates or

replace in transaction all amounts with 890 for amount = 340 and transactioncode = "0001"

or even

update transaction set amount = 890 where amount = 340 and transactioncode = "0001"

hth

MarK

 

[Mandy_crw]

Thanks Mark... actually ive used UPDATE... but for some reason it sometimes it works sometimes not... but I'll try REPLACE... Thanks again Mark...

 

[mjcmkrsr]

Hi Mandy,

Maybe REPLACE won't work either if you cannot determine the reason why UPDATE sometimes works and sometimes not

hth

MarK

 

[Chriss Miller]

The structure of your transaction table is missing a unique ID column, every table should have that, no matter if it's a main table or not. Especially the detail/child tables that have a reference to a main table are not identifying records by that reference uniquely, i.e. you have many transactions for the same person/account in the main table. To have an ID in every table makes it possible to identify the records unmistakenly. It's also bad, in this specific case, to not record anything else about the transaction. It's done at a specific date, for example. That's information you should store, otherwise even an id can only help to reidentify the order in which transactions were made, if it's an autoinc integer, but it won't tell you the exact date or even datetime of the transaction.

In short the topic is data nomalisation and other standards. You can read about them in a lot of places all over the internet and in books, not just specific to VFP, but about databases in general.

 

[Chriss Miller]

Very specifically in VFP, as MarK already posted and recommended, you have the concept of the current record in a workarea, in which you open the table. UPDATE-SQL does not have that concept, but REPLACE only replaces in the one, current record, unless you add the ALL or a FOR filter clause that covers alöl or multiple records. UPDATE by default always covers all records and only has WHERE to reduce them like a FOR clause does for REPLACE. So for SQL it is really essential to have a record identifier to be able to use a WHERE id=x condition to specify exactly that one record by its unique ID. It's not taking a single thought for a normal databse developer to have the Id field as first field of every table.

You can omit it in one case of a table storing n:m relation, two refernce (foreign) keys pointing to two other tables. Such tables are in use to cater for cases like two students and courses. Each student can be assigned to multiple courses, and in each course multiple students participate, so you can't just add a CourseId field into a student record, which would only allow a student to have one course, nor can you have a StudentId in a course record, or the course could only have one student. So you have a studentcourses or a coursestudents table, and how you name it is just a matter of case and your point of view, It will have pairs of StudentIds and CourseIds. If you browse for one StudientId you get a list of all the courses the student takes, if you browse for one courseId, you get the list of students of that course.

The point I wanted to make is here a separate record Id would identify such a key pair. Is it worth that? It's not necessary at first blush, because the restricting the key pairs to be unique is enough. But it can be done, and it can indeed become necessary, especially if you again take in the dimension of time and see that such data would not cover that the same course has different students in different years or semesters and that students also not participate in some course forever, but courses have an entry date and an end date. Some of that data could also be stored in the course, for example. If you add in all these additional information, records of such an n:m (aka many-to-many relationship) have more "flesh" to them and you can therefore also say it's never wrong to even give such data its own identifier column, no matter if it would also be sufficient to define the restriction to not allow duplicate Id key pairs, i.e. if a student and course are related by their ids, it doesn't change the information, if there would be a second record with the same pair of Ids, but that could cause trouble and put double the same students and/or courses into a result with more complex join structure. But it does become okay and even necessary, if a student repeats a course. No matter if you store the time information, too, or not, it would never be a problem with an Id in a record. So never go without an Id.

If you don't want to get into any details of data normalization theory for now or even ever (you always will get into some trouble about some structure you don't think through by the normalization rules, so never learning it is a very bad idea) the one thing that already solves a lot of problems ahead of time is giving each and every yet so unimportant table, even a log table just storing logging messages an id identifiere and in most cases a datetime will also be a great information to have.

 

[Mandy_crw]

Ok Mark... maybe ill have to review my codes well... Thank you Chriss.. you always givie an enlightening answers.... God bless... Ill try using an autoinc for identfying...

 

[mjcmkrsr]

... and to illustrate Chriss's explanations - post above paragraph 2 - please see demo code below.

SET DELETED ON

CLOSE ALL

&&& Create cursor with names

CREATE CURSOR curNames (cPKey C(4), cName C(10))
INSERT INTO curNames VALUES ( "1000","Joe")
INSERT INTO curNames VALUES ( "1001","Sam")
INSERT INTO curNames VALUES ( "1002","Jill")
INSERT INTO curNames VALUES ( "1003","Mary")
INSERT INTO curNames VALUES ( "1004","Zoe")
INSERT INTO curNames VALUES ( "1005","Jack")

&&& Create cursor with Subjects

CREATE CURSOR curSubjects (cPKey C(4), cSubject C(10))
INSERT INTO curSubjects VALUES ( "1000","English-1")
INSERT INTO curSubjects VALUES ( "1001","Math-1")
INSERT INTO curSubjects VALUES ( "1002","French-1")
INSERT INTO curSubjects VALUES ( "1003","History-1")
INSERT INTO curSubjects VALUES ( "1004","Chem-1")
INSERT INTO curSubjects VALUES ( "1005","Physics-1")
INSERT INTO curSubjects VALUES ( "1100","English-2")
INSERT INTO curSubjects VALUES ( "1101","Math-2")
INSERT INTO curSubjects VALUES ( "1102","French-2")
INSERT INTO curSubjects VALUES ( "1103","History-2")
INSERT INTO curSubjects VALUES ( "1104","Chem-2")
INSERT INTO curSubjects VALUES ( "1105","Physics-2")


&&& Create cursor - who has taken which subject

CREATE CURSOR curXCross (cFKNames C(4), cFKSubjects C(4))
INSERT INTO curXCross VALUES ( "1000","1000") && Joe takes English
INSERT INTO curXCross VALUES ( "1000","1001") && Joe takes Math
INSERT INTO curXCross VALUES ( "1000","1003") && Joe takes History
INSERT INTO curXCross VALUES ( "1001","1000") && Sam takes English
INSERT INTO curXCross VALUES ( "1001","1003") && Sam takes History
INSERT INTO curXCross VALUES ( "1001","1004") && Sam takes Chem
INSERT INTO curXCross VALUES ( "1002","1000") && Jill takes English
INSERT INTO curXCross VALUES ( "1002","1002") && Jill takes French
INSERT INTO curXCross VALUES ( "1002","1003") && Jill takes History
INSERT INTO curXCross VALUES ( "1003","1003") && Mary takes History
INSERT INTO curXCross VALUES ( "1004","1001") && Zoe takes Math
INSERT INTO curXCross VALUES ( "1004","1004") && Zoe takes Chem
INSERT INTO curXCross VALUES ( "1001","1002") && Sam takes French
INSERT INTO curXCross VALUES ( "1003","1103") && Mary takes History 2

&&& Create cursor with all possible combinations

SELECT curNames.cPKey as cFKNames, curSubjects.cPKey as cFKSubjects FROM curNames, curSubjects ;
    INTO CURSOR curNamesBysubjects READWRITE
    
*!*    BROWSE

&&& Create cursor with the subjects NOT taken

DELETE curNamesBySubjects FROM curNamesBySubjects ;
    JOIN curXCross ON curNamesBySubjects.cFKNames = curXCross.cFKNames AND curNamesBySubjects.cFKSubjects = curXCross.cFKSubjects
    
*!*    BROWSE
    
&&& Show names with subjects  - by name-subject

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
    LEFT JOIN curXCross ON curNames.cPKey = curXCross.cFKNames ;
    LEFT JOIN curSubjects ON curSubjects.cPKey = curXCross.cFKSubjects ;
    ORDER BY 1, 2 ;
    INTO CURSOR curByName

LOCATE
BROWSE TITLE "ALL Students' names - with one or more subjects or NULL"

&&& Show names with subjects - by subject-name

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
    RIGHT JOIN curXCross ON curNames.cPKey = curXCross.cFKNames ;
    RIGHT JOIN curSubjects ON curSubjects.cPKey = curXCross.cFKSubjects ;
    ORDER BY 2, 1 ;
    INTO CURSOR curBySubject

LOCATE
BROWSE TITLE "ALL Subjects - taken by one or more students or NULL"

&&& Show names with subjects NOT taken - by name-subject

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
    JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
    JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
    ORDER BY 1, 2 ;
    INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE TITLE "Subjects NOT yet taken by student ..."

SELECT curNames.cName, curSubjects.cPKey, curSubjects.cSubject FROM curNames ;
    JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
    JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
    WHERE curNames.cName = "Joe" ;
    ORDER BY 1, 2 ;
    INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE TITLE "Subjects NOT taken by Joe"

SELECT curNames.cName, curSubjects.cPKey, curSubjects.cSubject FROM curNames ;
    JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
    JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
    WHERE SUBSTR(curSubjects.cPKey,2,1) = "1" ;
    ORDER BY 1, 2 ;
    INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE  TITLE "Subjects NOT taken - sub-category x1xx NOT taken"

SELECT curNames.cName, curSubjects.cPKey, curSubjects.cSubject FROM curNames ;
    JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
    JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
    WHERE SUBSTR(curSubjects.cPKey,2,1) <= "2" ;
    ORDER BY 1, 2 ;
    INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE  TITLE "Subjects NOT taken - sub-category x0xx and x1xx NOT taken"

SET DELETED OFF
CLOSE ALL

Enjoy

MarK

 

[Chriss Miller]

Great data example of MarK. Notice that many-to-many relationsips are not rare, but still simpler 1-to-many relationships are more normal. And the curXCross in MarKs example, which I suggested to either be studentcourses or a coursestudents is nothing else but two 1-to-many relationships that point to two records to join them.

The sample code shows how great this construct can be used to get several lists depending on the current need you have to look at the data. From the point of view of a course, a student, Also a question, whether a subject is not taken by any student is possible to ask. So relationships of data are all very important to ask from different point of views, which is also a reason views ar called views. And the basis always is to have a unique identifier in each table.

Beginners tend to create tables for each concern and store lists that get redundant information when they grow, in the worst case contradicting information. To be very clear, a good databse structure also can't hinder you to have a wrong amount of some transaction, that's just a data problem not solvable by a correct structure, but having a uniqiue identifier is alsways making it simple to only access and in your case delete that one record, after the first step of identifing the one record that has the amount you know is wrong and is related to the person/account you know is involved. If you have two records with the same information, then additional data like the date of transaction is surely helpful and on top of that anything else you could have known, like the user that entered that transaction stored into the transaction record. So relating data to each other always makes it more accurate and easier to identify and filter.

Another point of view of this is that a database like that of a school, college, university and similar about students will store most all data about students, the least of which are just about the student themselve, like the birthdate or matriculation/enrollment date. That's single values you can store in the student. Anything else like student participation in courses etc. is needing a placeholder for the student that refers to it, the studentid or in this case, the unique registration number. Within databases it's not rare to not use such numbers, even though they are meant to be unique, but still go for a autoinc or GUID identifier. What's important is that the IDs store in other tables are so generally and universally usable that it's hurting to see how many people copy actual names of persons or other data into lists, because that makes them better in the aspect of human readability. Data that mainly consists of a series of Ids will not tell you something, when browsing it, unless you're a savant in remembering which person belongs to any Id.

But that's what joins or in VFP also SET RELATIONs are used for, you have an Id, you can lookup any other information about that record knowing a) the Id value and b) in whcih table to look for. And those are the two properties defining a foreign key, a) also means a foreign key is not accepted, if by a value you want to store you don't find a record in the table known by property b). Well, and there also beginners make the mistake to just define a field with the meaning, without actually telling the database the definition of that relationship. And not knowing that you can even, and you can establish restruction and rules, depeing on which database. In VFP you can define what's called referential integrity and based on definitions of fields and into which table they point to, let VFP create database trigger code that ensures these rules whenever you insert/append, update/replace, or delete data. You even have a few choices, for example defining cascading deletes: If you delete an order, all order items are deleted automatically. Or the inverse, only allow deleting an order when all order items are deleted first and the order becomes an empty, orphaned record. So you can define rules that make data maintanance simpler or stricter.

 

[Mandy_crw]

Thanks Mark and Chriss... thats an overwhelming and great information... actually you're right Chriss, as a beginner right now i do have a lot of table, but with Mark and You has explained... i gaining more knowledge to use cursors... Thanks to both of you, God bless... BTW update worked well in editing my table... thanks again...

 

[Doug Lindauer]

Mandy, I'm not sure you've gotten it yet. Let me focus just on what you said and wrote and give the simplest explanation. (Apologies to Chriss and mjc for rehashing what you've already said.) First off, you didn't give enough information for anyone to answer your question without making a lot of assumptions. You needed to do more than just say that you need to update the transaction table for a specific idnum. Which idnum? The one from the main table or the transaction table? We don't even know if your database design has the main table's idnum embedded in each transaction. So you need to show us EXACTLY what you're talking about by putting in the structure of the tables, not just the 2 columns you showed from each table.

So I'll have to assume we're at a beginner's level here. You seem to have unique id's in both the main and transaction tables. That's good. But you didn't indicate that you had a column in the transaction table with the id of person who made the transaction. That is necessary. Do you have that? Otherwise you never know who made the transaction. So let's say that you DO have that in a column called PERSON_ID. Then to edit the transaction for a specific person. For example, you would write:

update transaction set amount=<correct amount > where amount=<wrong amount> and PERSON_ID = <the idnum of the person who needs to be corrected>

Again, because you weren't specific, it's hard to give a complete answer without writing a treatise on database design and manipulation. But this should be a starting point. If you want to put another post giving your structures and the specific type of updates we can give you specific, clear examples.

 

[Chriss Miller]

So I'll have to assume we're at a beginner's level here. You seem to have unique id's in both the main and transaction tables. That's good. But you didn't indicate that you had a column in the transaction table with the id of person who made the transaction. That is necessary. Do you have that?

I interpret Mandy's explanation of her tables as the main table having an idnum and the transaction tables first column is referencing that, but has no own idnum. Take a look at the 3 001 values in the transaction table. That's not it's primary key idnum, for sure, as that wouldn't allow double values, not to speak of triple values. That's why I pointed out the importance of each table having its own unique id number.

 

[Doug Lindauer]

Absolutely correct Chriss. I didn't look closely enough at that.

But, for me, I don't always create a surrogate key like an id number unless it must appear in a related table as a foreign key. It's fine but it seems like mostly an unnecessary addition. I can't argue with someone doing it as a general practice thinking that it might be needed for some future expansion of the database. Actually, I have done that myself in transaction tables. What would you use it for other than being able to know the order in which it was entered? And the recno() gives you that anyway unless you're in the (usually bad) habit of sorting the table. Even if you delete and pack, the recno() order will still stay the same.

If I'm missing something there, point it out. I don't claim to know everything so I'm always open. I can't recall ever needing to refer to specific transaction id's.

In reality there's a lot of other things Mandy didn't show which may or may not be in the structure. You would usually want to have a date/time field and you would also have a header table in between the two tables so there are a lot other details.

 

[mjcmkrsr]

Hi Doug,

You'll need this unique record identifier at the latest asa your table is part of an updatable view

And the recno() gives you that anyway unless you're in the (usually bad) habit of sorting the table. Even if you delete and pack, the recno() order will still stay the same.

Your're right - the RECNO() order won't change but the RECNO() WILL change, and you won't be able to find the formerly associated record

hth

MarK

 

[Chriss Miller]

Even if you delete and pack, the recno() order will still stay the same.

That's true, but you wouldn't have a constant identifier. And that wouldn't make it a primary key.
Edit: And that's what MarK also posted before me.

So the question becomes, whether it is okay to have a table without a primary key. You find a lot of discussion about it. One major point is it never hurts to have it. So why omit it? To save 4 bytes per record? Or even 32 to 38 if you go for a GUID and either or not include its minus signs and/or curly brackets.

So if you omit a primary key, that's possible in database theory, it makes a table just a heap. A heap should not be involved in a relationship between data. Not on both ends. So in Mandys case also not the detail table. Or you get the problem she has. You can only identify a single record for an SQL-UPDATE or SQL-DELETE, if you're lucky that there is a unique combination of values. In her case, that the unwanted transactions with the 340 and 350 amounts that should be mended to 890 and 670 respectively are unique within the transactions of the given account identifier. She didn't even specify it has to be account 0001, but that's the only with both of the wrong amounts. It's okay in this case, but only considering the data shown. As Mandy asked: What about when you have to consider thousands more records? It becomes more and more possible the 340 and 350 amounts are not unique.

In databases you don't want to consider luck a necessary ingredient for maintaining it, do you?

You can argue that in VFP the concept of opening a table and browsing it, navigating to a current row and then just using the xBase DELETE (NEXT 1) deleting the current row will always enable you to do that. And it's not just a feature you only have available as developer browsing the data, if you have UI that allows the user to select the record in a grid or listbox or even just navigate to it showing in a single textbox, you still have the current row concept. That's unique to VFP, though. And not a thing available in databases, generally.

So in the end, it's a problem you could say is unimportant in VFP, but it still means you would at least need to browse data and identify the records to maintain manually, you could not generally let only code identify records, you could list them and code to take the last one.

I admit, even with a primary key in Mandys described problem you could still have multiple records with 340 and 350 amounts and the primary key would only tell you which of them were added later, the current recno (no matter if it differed in the past due to PACKs done in the lifetime of the table) also tells you that. So you would need a manual decision, anyway.

That's why I also argued about adding data like the user who entered the transaction and a transaction date. The more data to have, the better you can identify the problematic data. The most relevant there would be a record of a cheque or bill or anything that states the debit or credit so you can verify the amount change by the transaction is correct, you could even omit the tranaction amount and take it from that related record, well, or let that table have the role of the transaction table. And you know what other data is usally attached to a cheque or bill or other such things: A unique identifier, an issue date, etc.

So in the end for generality you have a primary key mainly for databases where you can only think of a table as a set, without having the VFP concept of a current record. If in MSSQL you have two records 0001 340 in a heap ( a table without primary key) There is nothing to pick one record of them to update or to delete. SQL needs a unique key to address one of them only. And though VFP has that current record concept, I would still never recommend to have heap tables of anything else but logs in which addressing them never is a problem.

To give one more argument: In a table you regularly pack, you don't have long term gaps in the row numbering, as RECNOS() only ever have gaps due to currently deleted not yet fully removed rows that await the next PACK. And so you don't have a documented deleted identifier, ever. A gap is also an information. If you ever print a record id, that paper never changes with database changes, but would have witness of a record it pointed to and can be verified to be deleted or still present in the database. Now I open another can of worms as the general advice is to not print keys, as they are just surrogate and not actual data, but for that case it would be useful. You don't want to have moving targets by PACK renumbering records by removing gaps.

So in very short: Database theory doesn't care if your database offers a current record concept or not, so the rules require a table to have a key that's unique and non null and constant over the record lifecycle as the full extent of rules about it. VFPs RECNO doesn't fulfill the last aspect.

Finally the valid argument for a table without primary key is that it's a heap, which is allowed in all databases. A heaps rows can not be identified unless you'd restrict them to not have duplicates, i.e. make all columns a combined primary key. The fewer columns you have, the harder it is, in Mandy's two column transaction case it would never allow two transactions with the same amount to have identifiable rows. Well, and we're then talking about making a heap a normal table again, just with an awkward key. So a "real" heap would mean you have to allow duplicates which you could only handle with a concept like the current record to get a handle on one of them and only one of them and not their duplicates. Even if you argue storage of data is always in some form of file, so there always is a difference in the physical position of that data, be it in a VFP recno() or in a page structure or whatever, it's just not something straight forward available to you and natural to use in the SQL DML (the part of SQL about data manipulation in contrast to data definition DDL like CREATE TABLE/ALTER TABLE etc.). Notice VFPs recno() is not part of DML by that definion, The data itself has no recno(), that's only available as DBFs have records in physical order at a record number. Like already stated similarly any other database has a physical positon of a record however the data storage engine works, but RECNO() is not univeral. You don't want to introduce need of special treatment of cases because you omit a key, that's cheap and easy to have in all your tables. Don't use heaps unless you actually can treat data as heap, as in logs you can also ZAP or truncate or drop and recreate without loss of essential data to the whole database.

 

[Chriss Miller]

To carve out the essence in Mandy's case and in the light of Doug Lindauers question what's the use of a primary key in tables that are not referred to in other tables, i.e. not ever used as foreign keys.

Technically you can have such tables without primary key, they are called heaps. And you can have foreign keys in such tables, too, technically. So doesn't that prove database technology supports that and makes this a norm?

Any table that is a detail table (to give an example an orderitems/orderdetails table in which, of course, very importantly every record points to the order it belongs to, but there's nothing else that would refer to it

I think it's a good example to look at things like an amount/count of the ordered item. What if the customer wants to change the orderitem in that amount/count aspect before the order is fulfilled? How do you actually address the single item in that case, if it has no key itself? In essence that's the reason to give every table and record a unique identifier, to be able to address it precisely only it and therefore maintain and edit it.

Specifically in VFP you can make use of the RECNO, using a grid to display and edit indirectly makes use of the RECNO, as a control VALID writes back to the controlsource llike a REPLACE does in the current record.

But even having that ability: If you're tasked with a data correction that doesn't just involve a single record or order but is a general problem, don't you think the availability of a key for each record is helpful? You could really be in a darn position to not be able to mend your data or would need to do a lot of tedious manual working through all the data.