| Author |
 Topic  |
|
|
yan00s
Moderator
405 Posts |
 Posted - 22 Feb 2013 : 17:33:27
|
Since the database got normalized for version 8 - with a few exceptions / inconsistencies when it comes to series / episode treatment - I'd suggest to go yet one step further and introduce referential integrity including linked updates / deletes for referenced records. Why so?
(1) It would keep the db in a more consistent state. We've got a lot more information to store (editors, cinematographers, producers, musicians, ...). This just calls for a stringent organization of records.
(2) It would make managing related records easier: JET will take care of dependencies. Updates to a master key (like the movie id) will get propagated automatically. Changes to master keys (like movie id renumbering) would be easier to accomplish. Deletes would include all affected records. Certain maintenance tasks currently not available on the front end would not even need any additional coding.
(3) (in part like (1)) No orphans in the db. With properly designed relations and enforced integrity data won't get messed up when something somehow goes wrong "on the front end".
My story here: I've switched my "production" db to version 8 a few months ago. Once in a while I've checked db health - but a thorough analysis after creating relationships (and a few indexes) the way I believed they should be I noticed that all in all there were indeed thousands of orphaned records distributed over dependant tables in the db. And I'm talking about a db which contains just approx. 3000 movies and 30000 people details. That's not good, not at all.
Dependencies (draft snippet):
+--------------+ +--------------+
|Master table 1| |Master table 2|
|Movies |--- |People |
+--------------+ | +--------------+
| | |
| +-----------------+ |
| |Master subtable 1| |
| |Episodes | |
| +-----------------+ |
| | |
+--------------+ +-----------------+ +--------------+
|Master Key | |Master Key | |Master Key |
|Movie ID | |Episode ID | |People ID |
+--------------+ +-----------------+ +--------------+
| . |
| . |
+-----------------+ . |
| ActorsLinkMovie | . |
| Dependend Key1: | . |
| MovieID | . |
| | . |
| Dependend Key2: |---------------------------
| PeopleID | .
| |
+-----------------+
For more details I've got an empty example db with relationships / dependencies set like I think it should be. Alessio, if you want I could send it to you.
"Possible Cutbacks / ToDos":
(1) Like already pointed out here: http://www.binaryworks.it/forum/topic.asp?TOPIC_ID=10805 - program logic must ensure that updates always follow the rules: never try to insert a dependent record before its ancestor. My guess is that this would not require too much programming and would be good practice even without any forced integrity settings on the db level.
(2) All in all XMM would probably become more unforgiving to hiccups when data processing is involved. A detailed error logging could be required (what went wrong, like: which people info could not get added - current status line JET messages aren't sufficient). Regular backups will be a must as well. While the latter should mainly be a user's responsibility XMM may be better of enforcing some protection especially before batch updates.
(3) Some sort of migration / cleanup scripting might be required to make existing (messy) databases ready for referential integrity. My guess is that needs quite some effort. Maybe XMM could offer referential integrity as an option and only enforces it for newly created (empty) databases.
So, any thoughts on that?
PS: I've spent quite some time with version 8 now - my forum posts from the last few weeks are a good indicator for it, I suppose. While I surely will keep an eye open on "my" topics I will refrain to mainly checking the release notes for updates instead of pushing things via the forum. |
. |
|
|
JDommi
Administrator
Germany
4655 Posts |
Posted - 22 Feb 2013 : 18:48:15
|
One big problem:
Ale manages the tables by code. He doesn't use the Keys...
Otherwise I fully agree to you. |
In order to achieve what is possible, you have to try the impossible over and over again.
Hermann Hesse |
 |
|
|
yan00s
Moderator
405 Posts |
Posted - 22 Feb 2013 : 19:23:31
|
quote:
Originally posted by JDommi
One big problem:
Ale manages the tables by code. He doesn't use the Keys...
...
I know - or better: I believe to know.  As far as I do unterstand the topic (I'm not really into programming) the code (VB .NET) makes use of the JET engine (it has to). JET is fully capable to handle "the rest" database action wise - for instance it understands SQL and exposes management functions like relationship building for Access dbs. So it should not really matter that the tables are managed by code. Except that the code has to be written and maintained (which is a problem with just one Alessio ...).
The indexes (and keys) are mostly there already anyway. Even so without relations they might not serve any real purpose right now. |
. |
|
|
|
JDommi
Administrator
Germany
4655 Posts |
Posted - 22 Feb 2013 : 23:28:33
|
That's right, one Alessio sometimes seem to be another one too less  |
In order to achieve what is possible, you have to try the impossible over and over again.
Hermann Hesse |
|
|
|
apd
Junior Member
Greece
378 Posts |
Posted - 23 Feb 2013 : 09:09:38
|
Well, it sure looks interesting, and I guess it might resolve, at least partly, one of the main issues of XMM8, which is speed (or, rather, the lack of it).
The thing is, this forum never missed good ideas; the problem lies with Alession's priorities. He has never explained what is the scope of v.8, what he wants to accomplish, and as far as what point it will be developed. Your idea looks more suitable for a major new release, not as an incremental step - esp. with so many bugs and issues still pending.
BTW, we waited 2 week for the upd 8049 and, if the changelog is complete, all it offers is one (1) minor bug fix, one new function, and two additions. Alessio is advertising v8 as a working, official version, and at the same time I see a lot of frustration in the forum, so something must be really wrong.
To speak personally, I don't mind being a beta tester, as long as at least some of my suggestions are accepted in the long run. This doesn't seem to be happening. For quite a while now, I feel that I put up with XMM just because of the promises it leaves open without fulfilling them. I'm dangerously reaching the point to consider it a waste of time and I'm afraid that if that's the way I feel after 7-8 years of maintaining an XMM db, then Alessio has some serious thinking to do about the newer users who don't have such an investment to lose. |
|
|
|
Odin
New Member
Norway
67 Posts |
Posted - 02 Mar 2013 : 17:29:56
|
I would really like to see xmm use a different database.
There are some pretty good alternatives that would be a lot faster than Access. For example SQL CE, SQLITE, FireBird they are all standalone databases and do need anything other than a .dll reference added to your project. With a faster database xmm should speed up quite a bit. |
|
|
|
yan00s
Moderator
405 Posts |
Posted - 02 Mar 2013 : 18:07:02
|
Well, I surely didn't want to trigger a discussion about which database to use. 
For two reasons - (1) because I don't believe XMM is anywhere near the limits of Access. And Access is a well integrated product operation system wise. (2) Access gives me an easy to handle interface for maintaining my movie db.
The speed issues we experience do not originate from MS Access - I'm rather sure of that. I would point my finger at the DevExpress components used and - just maybe - the way those are tied together in XMM. Especially the Grid and operations happening inside its (program) space seems to be a bottle neck when it comes to data intensive tasks. Good (bad  ) example:
If you happen to have more then 10K people in your database you cannot really use the function "Find duplicate people" - takes ages. The same operation needs just up to two seconds on db level ... |
. |
|
|
|
Odin
New Member
Norway
67 Posts |
Posted - 02 Mar 2013 : 21:57:30
|
You're saying if you open the access db (in access), it doesn't take long to find duplicate people. But inside XMM it takes a long time to run?
I wonder why the grid would be slow though, never used any of devexpress components (seems to be pretty popular though).
I noticed in a backup I had of xmm7 there was a System.Data.SQLite dll (sqllite ado.net adapter). I was curious if this was intended for xmm as a db solution, but never happened? |
|
|
|
yan00s
Moderator
405 Posts |
Posted - 03 Mar 2013 : 00:11:15
|
quote:
Originally posted by Odin
You're saying if you open the access db (in access), it doesn't take long to find duplicate people. But inside XMM it takes a long time to run?
That's what I'm saying. Those special filters on the grid are painfully slow. If you happen to have a lot of actors - and a lot starts quite early - you will stay away from it.
Doing this outside the grid is much faster. If you save the following code as an *.hta file and adjust the db connection string so it finds your file you could compare for yourself.
<script language="VBScript">
Sub DupsName
Set conn = CreateObject("ADODB.Connection")
' Connect to the database - adapt source if necessary
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\PathTo\xmm.mdb"
conn.Open strConnect
' find duplicates
StrSQL = "SELECT People.[Name], People.[Web_ID], People.[PeopleID] FROM People WHERE (((People.[Name]) IN (SELECT [Name] FROM [People] AS Tmp GROUP BY [Name] HAVING Count(*) > 1))) ORDER BY People.[Name];"
Set rs = conn.Execute(StrSQL)
' fancy lines
itemnr = 1
linestyle = 0
document.write "<body fontstyle='sans serif'><H1 color='red'>Duplicate People Entries (NAME)</h1><table borderstyle='1px grey'>"
document.write "<tr bgcolor='red'>"
document.write "<td width='20%'> Item #</td> <td width='50%'> Name </td> <td width='20%'>People ID</td>"
document.write "</tr>"
' print out the records found
Do While not rs.EOF
if linestyle = 0 then
document.write "<tr color='black' bgcolor='white'>"
linestyle = linestyle + 1
else
document.write "<tr bgcolor='silver'>"
linestyle = linestyle - 1
end if
document.write "<td>" & itemnr & "</td><td>" & rs.Fields("Name") & "</td><td>"& rs.Fields("PeopleID") & "</td>"
rs.MoveNext
itemnr = itemnr + 1
document.write "</tr>"
Loop
document.write "</table></body>"
End Sub
Sub DupsNameID
Set conn = CreateObject("ADODB.Connection")
' Connect to the database - adapt source if necessary
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\PathTo\xmm.mdb"
conn.Open strConnect
' find dups
StrSQL = "SELECT People.[Name], People.[Web_ID], People.[PeopleID] FROM People WHERE (((People.[Name]) IN (SELECT [Name] FROM [People] As Tmp GROUP BY [Name],[Web_ID] HAVING Count(*) > 1 And [Web_ID] = [People].[Web_ID]))) ORDER BY People.[Name], People.[Web_ID];"
Set rs = conn.Execute(StrSQL)
' fancy lines
itemnr = 1
linestyle = 0
document.write "<body fontstyle='sans serif'><H1 color='red'>Duplicate People Entries (NAME)</h1><table borderstyle='1px grey'>"
document.write "<tr bgcolor='red'>"
document.write "<td width='20%'> Item #</td> <td width='30%'> Name </td> <td width='15%'>Web ID</td> <td width='15%'>People ID</td>"
document.write "</tr>"
' print out records found
Do While not rs.EOF
if linestyle = 0 then
document.write "<tr color='black' bgcolor='white'>"
linestyle = linestyle + 1
else
document.write "<tr bgcolor='silver'>"
linestyle = linestyle - 1
end if
document.write "<td>" & itemnr & "</td><td>" & rs.Fields("Name") & "</td><td>"& rs.Fields("Web_ID") & "</td><td>"& rs.Fields("PeopleID") & "</td>"
rs.MoveNext
itemnr = itemnr + 1
document.write "</tr>"
Loop
document.write "</table></body>"
End Sub
</script>
<body>
<H1>Speed Test</H1>
<H2>Jet Operations on MDB</H2>
<p color="red">Will only work with proper connection strings - see and adapt source if necessary.</p>
<input type="button" value="Find Duplicates (Name only)" name="run_button" onClick="DupsName"><br /> <br />
<input type="button" value="Find Duplicates (Name and WebID)" name="run_button" onClick="DupsNameID">
</body>
quote:
Originally posted by Odin
I wonder why the grid would be slow though, never used any of devexpress components (seems to be pretty popular though).
I noticed in a backup I had of xmm7 there was a System.Data.SQLite dll (sqllite ado.net adapter). I was curious if this was intended for xmm as a db solution, but never happened?
No idea but I doubt it. Maybe it was meant to be used by some tool or plugin. |
. |
|
|
| |
Topic |
|
Proposal: Referential integrity for DB