Yes, though it is limited. There is a "Table Relations" report in the data dictionary. It assumes that fields are named the same across tables. So if you have a "custnum" field in table A and a child table B with the same data in a "cust-num" or "B-custnum" field, that tool won't report on that table relation. Progress doesn't have a way of explicitly expressing table relationships in the schema, so if you haven't been strict about field naming then your only resources for finding relationships will be looking at your internal documentation or talking to the developers who understand your data model.Is there a specific tool in Progress that might show what our current table relationships are?
Hello dbacdog:I know user count or OS does not matter in this post. Just a habit.
Over the last 25 years we have added lots of tables, indexes, and modified over 5,000 programs. I want to know if our database is normalize or not. From a front end perspective, I don't know if users are inputting the same data more then once or twice, etc... I see tables that have the same data, however, does not mean it is a bad thing,......I just thought maybe there is a tool, or some other way to see if our database is good.
If you don't have ERwin, you can do: Data Dictionary / Reports / Table Relations / All Tables. Although this will take a long time, verify that indexes are too many or access the same data.I know user count or OS does not matter in this post. Just a habit.
Over the last 25 years we have added lots of tables, indexes, and modified over 5,000 programs. I want to know if our database is normalize or not. From a front end perspective, I don't know if users are inputting the same data more then once or twice, etc... I see tables that have the same data, however, does not mean it is a bad thing,......I just thought maybe there is a tool, or some other way to see if our database is good.
Why?Also with so many tables, it would be good to see if it can be split into more than one database
Because that significantly improves performance, according to a system, it groups tables according to the subject matter, so we can have: treasury, accounting, distribution and logistics modules, suppliers, clients, etc. If it is not ordered, it can become a mess, both for modification and maintenance.Why?
The multi-database approach, especially when applied to an existing single-database application, sounds more like a "mess" to me, however aesthetically pleasing it might seem to a developer. And it is not clear to me why a single large database should be assumed to be "not ordered".If it is not ordered, it can become a mess, both for modification and maintenance.
My vision is that of a developer, if you need to do an audit of systems, it is clearer to face this by modules, this separates tables based on the subject they deal with, while some programmers are using another model of the database and do not have to leave , so that they modify the entire database. This is real and I know, we did it in highly complex systems.I understand the appeal of treating databases like containers so you can mentally compartmentalize components by their business function. But I can't agree with a blanket statement that it "significantly improves performance"; particularly when you're talking about someone else's application that I have to assume you have no prior knowledge of.
Let's imagine we take an application with the functional areas you describe and we split it into n databases, where n=5 (or more?). That in itself is a project that involves time, risk, and cost. Assuming you do that successfully, what is the future ongoing impact on operational cost and risk management?
You now have:
The above is not to say that there are not multi-database applications that work well and perform well. But the original poster is best served by being able to make an informed decision, weighing costs against benefits. It is not as if splitting one database into many is some silver bullet that magically makes everything better.
- n database backups to complete and verify each night, rather than one;
- n sets of AI files to monitor, rather than one;
- n warm spare or replica databases to maintain and monitor, rather than one;
- n sets of TCP ports to allocate, per broker, rather than one;
- n times more database processes (brokers, servers, page writers, utilities, monitors, etc.), consuming more CPU cycles and memory on the same hardware;
- n times more periodic maintenance tasks to do (e.g. index compact or rebuild, SQL statistics updates, dump and load, repair, schema update, structure update, policy updates, user management, audits, etc.);
- the possibility that a business transaction may complete in an inconsistent manner because it involves tables from different databases and therefore different database transactions that could complete or roll back independently;
- the possibility that in a disaster recovery scenario, you have valid backups and AI files for n-x of your databases but not for the other x databases. Then what? Will the databases restored to more recent points in time then have orphan records that are inaccessible? Or point to child records that don't exist in the other databases? Did the application developers plan for this? Are the failure modes understood?
- n times as many status and performance metrics to monitor; e.g. buffer hit ratio, logical to physical ratio, transaction rates, undo rates, long-running transactions, BI size and growth rate, latch waits, resource waits, read rates, space management, page writer performance, the list goes on and on.
- a fixed pool of memory split n ways; buffer pools, AI and BI buffers, caches for TDE, OM, CDC and more. What split is best for application performance? Will that always be the best split? Are you giving suppliersdb much more cache than it needs and starving clientsdb because it doesn't have enough? That's bad for performance. So you have to monitor, tune, restart both, and continue to monitor. But what if the next version of the application changes the I/O profile and now there is much less read activity in suppliersdb but much more in accountingdb, so again you are hurting performance. You could spend a lot of time chasing these problems you have created, rather than let a single database cache what it needs to, as the application, the business patterns, or the user behaviour change over time.
- There are probably lots of other downsides to doing this, but the above is what I came up with offhand.
The multi-database approach, especially when applied to an existing single-database application, sounds more like a "mess" to me, however aesthetically pleasing it might seem to a developer. And it is not clear to me why a single large database should be assumed to be "not ordered".
That is true, it is my turn as a developer and as an auditor, beyond the technology, if you work well in an orderly way, that will be detected in our project and I develop a methodology that identifies the tables of separated bases, to see if it is not They connect to give notice, it is because they may be indo modified in their structure.Important in this evaluation is that it is highly likely that some transactions will span databases, necessitating two phase commit and the risk of one side of that transaction failing.
What you point out is what happens in the environments in which I work, not only in my country, where the old school at the business level makes it very difficult to implement other forms of work. I recently saw bad architecture in a large base in Ukraine on migration, even worse than the gaps I saw here in my midst. It is more try to achieve scripts that control things that are usually left to groups of tasks and I have not been able to make them work due to what you indicate here.You could certainly organize things differently at the logical layer than at the physical layer.
Rob is absolutely correct that there are a lot of downsides to needlessly having multiple (physical) databases.
There can be some upsides, particularly if you're doing an old-school multi-tenant implementation where keeping client data separate is a requirement. Or if you have certain hardware constraints making themselves known. But those are fairly rare situations that can be dealt with quite separately from the logical design of the database.
Surely it is like that, most of the problems are rarely a single thing, I have had to see that the misuse of indexes, coupled with an unclear access, with a very saturated server, sometimes confuses.It is probably worth noting that, if the concern for performance is based on the existing unified database, it is quite likely that something else is the source of the performance issues, rather than it coming from a unified database.