October 25, 2018 at 9:18 pm
I'm facing an dilemma in something similar to a sales database with parent-child relationships between the tables "Country", "Region" and "City" where each table has an Active (soft-delete) column. If sales in a country shut down, in our system the country's table's "Active" flag would be set to 0, indicating the country is no longer active and no new child records can be created against it. I'm unsure how to handle the child records of the now inactive record.
A developer seems convinced that if a parent record is marked as inactive, all child objects should have their Active flags overwritten to inactive as well. I believe that's unnecessarily destructive (it would overwrite the child active flags, making this difficult to revert), and that up-hierarchy records should be checked to evaluate whether a record is truly active.
I'd appreciate any recommendations on this; should the child records in Region, City, and Suburb also have their active flag updated, or should users querying the data be checking the active flags on the records up-hierarchy where appropriate?
Regards,
Andrew P.
October 26, 2018 at 7:33 am
It depends on your business requirements, so there is no hard and fast rule.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 26, 2018 at 7:41 am
Andrew P - Thursday, October 25, 2018 9:18 PMA developer seems convinced that if a parent record is marked as inactive, all child objects should have their Active flags overwritten to inactive as well. I believe that's unnecessarily destructive (it would overwrite the child active flags, making this difficult to revert), and that up-hierarchy records should be checked to evaluate whether a record is truly active.I'd appreciate any recommendations on this; should the child records in Region, City, and Suburb also have their active flag updated, or should users querying the data be checking the active flags on the records up-hierarchy where appropriate?
Regards,
Andrew P.
I'd tend to agree with you. A child table is never something that you should look at without checking the parent table so marking the parent table row as deleted is all you need to do.
October 26, 2018 at 7:57 am
If the requirement is that some table(s) not accept inserts or updates that refference inactive values, then my first impulse would be to create an "insteasd of" trigger that checks any INSERTED values against that series of tables and automatically applies or rejects new values based on the active flags. At least that way, you have some insurance against stored procedures and ad-hoc sql that don't have the check logic incorporated.
Of course, that does impose unnessary overhead when the queries do have the check logic included...
As to the question regarding a cascading update of child values, based on the parrent... I'd lean toward "no"... for the very reason you mentioned. You'd have no way of accuratly reverying the child values if/when you reverted the parent.
October 26, 2018 at 10:47 am
It seems kinda arbitrary to assume that if/when a "deactivated" Country reverted to "active", that the previous Active/Inactive flags would still be applicable to its child Regions/Cities. Maybe that's the case in your company, you would know that better than I. But I would normally assume that if a whole Country was "deactivated", that all its child elements would need to be freshly re-evaluated at such time that the Country was "reactivated"... in which case it makes perfect sense to flag all the child records as "inactive" at the time of Country deactivation.
If you need to store a history, it might even make more sense to store these things with Start/End Dates.
As others have said, it all depends on the specific needs of your business. Lots of solutions depending on what you need to do and what makes maintaining the system (including both your part and the developer's part) easier.
October 26, 2018 at 1:57 pm
Andrew P - Thursday, October 25, 2018 9:18 PM
If this was a wildlife forum, you’d be the guy who’s talking about grizzly bear eggs. In short, you’re missing some very fundamental concepts. Let’s make a list:
1. the terms child and parent belong to network databases and are never part of RDBMS. We have referenced and referencing tables in SQL.
2. In RDBMS, we have weak and strong entities. A weak entity requires the existence of a strong entity for it to exist. This sounds like child-parent but it’s far more general.
3. Putting "Country", "Region" and "City" in separate tables is wrong on two counts. First of all, tables model sets, so their names have to be either plural or collective nouns. But more important all of these are geographic locations that have a hierarchical relationship. This data modeling error is known as attribute splitting. It means that you taken things that should be in the same table and split them out into their own tables. Instead of having a personnel table, would you have a “Male_Personnel†and a “Female_Personnel†tables? That would be a split on the sex code into personnel table.
4. We use flags in assembly language and other filesystems decades ago. There were mechanical reasons for this, which no longer apply. In SQL, we classify the status data by using predicates.
I'm facing an dilemma in something similar to a sales database with parent-child [sic:not RDBMS!] relationships between the tables "Country", "Region" and "City" where each table has an Active (soft-delete) column.
>> If sales in a country shut down, in our system the country's table's "Active" flag [sic] would be set to 0, indicating the country is no longer active and no new child [sic] records [sic] can be created against it. I'm unsure how to handle the child [sic] records [sic]of the now inactive record [sic]. <<
When you take a data modeling class, sometime during the first week they should tell you not to mix data and metadata in the same table. That's exactly what your flags are doing!
>> A developer seems convinced that if a parent [sic] record [sic] is marked as inactive, all child [sic] objects should have their Active flags [sic] overwritten to inactive as well. I believe that's unnecessarily destructive (it would overwrite the child [sic] active [sic] flags [sic], making this difficult to revert), and that up-hierarchy records [sic] should be checked to evaluate whether a record is truly active. <<
Having seen this done several decades ago, I agree with your developer. What happens is you wind up with a lot of orphans filling up the disk. Then you have to write a utility to clean them up. The idiots for whom I was working at the time, had a different solution; they simply bought more disk for the client 🙁
>> I'd appreciate any recommendations on this; <<
Google “the nested set model†and use it will be able to eliminate subtrees in a single statement easily. Basically, the nested set model takes the idea of XML and puts it into SQL. All of the algorithms that you have for XML can be translated into your new language. I’m not going to post a few thousand words when you can research it yourself, but here something to get you started:
CREATE TABLE Locations
(lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > lft),
UNIQUE (lft,rgt),
location_name VARCHAR(20) NOT NULL,
location_type CHAR(1) NOT NULL
CHECK (location_type IN ‘C’ --Country
, ‘R’ –-Region
‘U’ -- City (urbo)
);
Please post DDL and follow ANSI/ISO standards when asking for help.
October 26, 2018 at 3:05 pm
jcelko212 32090 - Friday, October 26, 2018 1:57 PM1. the terms child and parent belong to network databases and are never part of RDBMS. We have referenced and referencing tables in SQL.
The goal of language is to communicate. We all understood exactly what he meant. Furthermore, whoever chose the terms referenced/referencing is an idiot, because the two terms are too similar. Parent/child are much more distinct, which is why they're in more common usage.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 26, 2018 at 4:36 pm
drew.allen - Friday, October 26, 2018 3:05 PMjcelko212 32090 - Friday, October 26, 2018 1:57 PM1. the terms child and parent belong to network databases and are never part of RDBMS. We have referenced and referencing tables in SQL.The goal of language is to communicate. We all understood exactly what he meant. Furthermore, whoever chose the terms referenced/referencing is an idiot, because the two terms are too similar. Parent/child are much more distinct, which is why they're in more common usage.
Drew
I disagree, based on teaching SQL for a few decades. I am willing to bet that his mindset is a parent and child model based on linked list that came with the original network databases. Since these systems were based on pointer chains, there was no way that a parent could be its own child, nor that a child become a parent. In fact, in most of the original database systems, a cycle was a disaster that would lead to endless loops because you chased down pointer chains to pick up the records (not rows), one at a time in a sequence. Concepts of next and prior are fundamental in the network model. But in RDBMS, we think in terms of completed sets.
The terms "referenced" and "referencing" came from ANSI X3H2; they might sound alike and the table can be both, but we wanted accuracy over other terms. Do you have trouble with all the words in English that use "<root>-er" and "<root>-ee" to how who does and who receives an action?
And frankly, they are in common usage because too many people don't even know the difference between a row (virtual or base table, has a key, who structure is defined in the DDL, not in the row itself, etc.) and a record and a record (just the opposite on those points). My wife and I was into downloaded audiobooks in the car. But because we are old we still say "books on tape" instead of the correct term. We haven't unlearned things. We can randomly access our audiobooks, but the original cassette tapes were sequentially searched, the hardware was completely different, the audio quality was completely different, etc.
Look at the postings here, and see how many people don't appreciate that a table is not a file. They use identity to attach a physical record number based on the insertion order just like we did with magnetic tapes instead of a correctly constructed key. They don't understand there is no next and prior concept in RDBMS because of its set oriented nature. A virtual tables just as much a table as a base table!
As I said I have found from teaching this language for a few decades, that if I can get a student using the right terms, they get the correct mental model and they suddenly start seeing things that they couldn't before. .
Please post DDL and follow ANSI/ISO standards when asking for help.
October 26, 2018 at 5:11 pm
Thanks for the replies. The "Country > Region > City >Suburb" hierarchy was a simplistic example of the dilemma I was facing. In the specific situation I'm working with the table definitions are more complicated and dependent on subject knowledge, with variance between the fields making it unsuitable for merging into one table. Apologies for not making that clearer.
drew: thanks - I appreciate knowing this is a case-by-case consideration.
Jonathan: thanks, I do expect there will be times when undoing a deactivation will be required due to user error.
Jason: that's a really good idea, especially where the rule is absolute - thank you. In the current situation application end-users shouldn't be able to select the record from a drop-down when creating a new dependent record, but if someone were to create a child record of an inactive record during data correction or manually, that wouldn't be an issue.
autoexcrement: Yes, I hadn't thought of that flaw with my example. You're right, it would be a flawed assumption to assume that should a company shut down operations in a country, then start operations again, that all previous offices, etc. would still be operational. Thank you for this - it's a good example of where cascading the delete down to dependent records is appropriate. I hadn't considered this. In the current situation, the only time a record would need reactivated would be if it were deactivated by accident or error.
Joe:
1. I'm unsure how to word the original post succinctly without using the term "parent/child relationship" or "hierarchical relationship". If I had said the below would it fit better?
I'm facing an dilemma in something similar to a sales database with a hierarchical relationship between the tables "Country", "Region" and "City"
3. table naming: I hadn't recognized I was doing this, thank you!
3. attribute-splitting: understood, thank you.
4. How do you refer to a bit column - would it be more correct to use the term "the active predicate"?
Thanks for pointing out my use of the term "record" where "row" should be used.
>> If sales in a country shut down, in our system the country's table's "Active" flag [sic] would be set to 0, indicating the country is no longer active and no new child [sic] records [sic] can be created against it. I'm unsure how to handle the child [sic] records [sic]of the now inactive record [sic]. <<
When you take a data modeling class, sometime during the first week they should tell you not to mix data and metadata in the same table. That's exactly what your flags are doing!
I can't see this - could you point out how I'm mixing data and metadata?
nested-set: I've checked out the Wikipedia page and understand how it is populated, thank you for introducing me to this concept.
October 28, 2018 at 12:32 pm
>> 1. I'm unsure how to word the original post succinctly without using the term "parent/child relationship" or "hierarchical relationship". If I had said the below would it fit better?
I'm facing an dilemma in something similar to a sales database with a hierarchical relationship between the tables "Country", "Region" and "City" <<
I'm going to show my age. Before RDBMS. We had lots of products based on a network or hierarchical models of data. IMS and IDMS are still in heavy use in the commercial sectors today, but there were lots of others. The first one I worked with was called Image/3000, which came on Hewlett-Packard equipment and was based on a product called TOTAL. I had a friend who worked for default commercial software company as the "database girl"; her job was to take their COBOL packages and attach all the available databases to them. Never mind that they were being used as simple files, and not taking advantage of any of the features they might've had these databases. All the company wanted was the claim of supporting the newest whizbang.
All of these things were based on pointer chains. For example, my Image/3000 had a master file, which was a hash table. The hash algorithm was so weak that it had to be pre-allocated with a fixed number of records and that allocation had to be a prime number! (We were a simple tribe who lived in trees and ate our children) . The records in the master or parent table had pointers to the chains of child records. The pointers only went one way and as I remember, you couldn't back up on the chain. If you been with SQL Server for a few decades, you might remember that the first cursors also had to be read in one direction only (this mimicked the first magnetic tape drives on mini-computers worked).
The various products invented their own terminology, because there were no ANSI/ISO standards. A lot of them started off with "slave – master", but that was considered politically incorrect, especially in the 1960s. This later became "parent – child" and that became the most common terminology.
Because we were dealing with linked list, the last things you wanted was a cycle that would lead to infinite looping in the COBOL host program, or a pointer that didn't go to a known record type in the child table, or broken chains.
The advantages of things like IMS are that they are insanely fast, very well optimized after all these decades , and designed to work with COBOL batch processing. In short, they're perfect for banking and insurance applications.
Your three-level "Country", "Region" and "City" problem would've been modeled with apparent hash table on countries, whose children held records for regions, and those records would have another identifier to a second hash table, whose children would be the cities. ARRGH!
3. table naming: I hadn't recognized I was doing this, thank you!
The ISO 11179 standards for names have also been picked up by the metadata group. Essentially. You want to be able to give data element names which can be used anywhere, not just a particular database. That means we stick to the Unicode subset of Latin one letters and digits and a very simple set of punctuations, without any spaces embedded in the names. The general format is:
[<role>_]<element name>_<element property>
Tables model sets, so they are plural or collective names. Back in the pre-relational days, when we work with files that do record processing, you can make a case for singular names. Things like IDEF did just that. Today, we would never say "Employee" we might use "Employees", but the preferred decision would have been to use "Personnel", the general collective term. For example, the table is "forest", the elements in this table are individual "tree", and there properties are "tree_diameter", "tree_species", and so forth. If the same data element appears twice in a query, then we know what role each of those occurrences plays in the data model. For example, "Captain_emp_nbr" and "first_base_emp_nbr" in a company baseball team table.
>> 4. How do you refer to a bit column - would it be more correct to use the term "the active predicate"? <<
I don't write with bit columns at all. I consider them to be part of assembly language. If I have a status code of some kind (employment_status, marital_status, etc.) . I know that it comes from the Latin for "state of being" and has to have Einstein's fourth dimension, time. Somewhere in my data model. I have to have the start of employment and the termination of employment dates. If I have an attribute that really does have just to values, and I try to encode those two values. The classic for English speakers has been to use "M" and "F" for male and female sex codes; the actual ISO sex codes are 0= unknown, 1= male, 2= female, 9= lawful person (corporations, organizations, etc.) . The BIT data type has been a real problem for SQL Server users. Originally, it was exactly what a computer scientist with think of as a bit. But then it became a numeric data type with a very limited range. But all numeric datatypes have to be nullable! People who had written with the assumption they were only going to get a one or zero and didn't have to do any checks on their schemas, got a rude surprise. They started getting nulls.
>> If sales in a country shut down, in our system the country's table's "Active" flag [sic] would be set to 0, indicating the country is no longer active and no new child [sic] records [sic] can be created against it. I'm unsure how to handle the child [sic] records [sic]of the now inactive record [sic]. <<
I would want to know when (the time interval) it was active. But you also need to look at this article:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/
>> I can't see this - could you point out how I'm mixing data and metadata? <<
The name of the data element should tell you what it is by its nature (in logic, this is the Law of Identity). The name should never include something to tell you how it's stored (that usually the data type encoded as a prefix or suffix on the name), where it is stored (putting a different table name on every occurrence of the same attribute),or how it's used (what's a foreign key in this table will be of primary key in another table; don't put PK and FK in the names).
Actually, my quick example of how to do a nested set model is wrong. Think about personnel versus an organizational chart. The personnel are pretty clearly entities and will need their own table. But the organizational chart of a company is a relationship (the other option for what a table can model). How many different people have been the president of the United States or the CEO of General Motors? There should of been a reference from the job to the employee who holds the job, and some indication of when they held the job. I have mixed entities and relationships in one table, which is sort of the opposite of splitting attributes.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 31, 2018 at 7:19 pm
Andrew P - Thursday, October 25, 2018 9:18 PMI'm facing an dilemma in something similar to a sales database with parent-child relationships between the tables "Country", "Region" and "City" where each table has an Active (soft-delete) column. If sales in a country shut down, in our system the country's table's "Active" flag would be set to 0, indicating the country is no longer active and no new child records can be created against it. I'm unsure how to handle the child records of the now inactive record.A developer seems convinced that if a parent record is marked as inactive, all child objects should have their Active flags overwritten to inactive as well. I believe that's unnecessarily destructive (it would overwrite the child active flags, making this difficult to revert), and that up-hierarchy records should be checked to evaluate whether a record is truly active.
I'd appreciate any recommendations on this; should the child records in Region, City, and Suburb also have their active flag updated, or should users querying the data be checking the active flags on the records up-hierarchy where appropriate?
Regards,
Andrew P.
How do you use those active/incative flags in your code?
Are you loading the full list of "active" cities and suburbs into the drop-down boxes from the beginning and then filter them out when a user has picked a country?
Well, it would be a waste of resources of both the server and a client machine.
But if you load a list of active countries only, let a user choose one and then load a list of active cities from that country - then there is no problem at all.
Even if a city from "incactive" country is marked as "active" there is no way a user can choose it - it cannot possibly apear on the list anyway, because its "parent" country cannot be selected.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply