Menu
▾
▴
Re: [Tsheetx-developers] Relational Database
|
From: Mark W. <ma...@rw...> - 2011-03-09 23:01:42
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
That is fair enough. As Dave points out, 'never touch a running
system'. This point does hold true as there would be someone
somewhere where a key constraint would fail which would mean a huge
amount of work to make any database compliant with the new
constraints.<br>
<br>
Maybe we could write in some PHP based dependancy stuff however, as
for instance (with reference to txhseet v1.2 ish) if a task is
deleted from the system the total hours worked on a project is then
affected even though the clockings still exist in the db tables. I
think we should update the code such that when something is
'deleted' it is just marked as deleted and then not shown up, to
prevent this sort of issue from occurring.<br>
<br>
So far the only constraints I have played with is the RESTRICT
versions by which you can't do anything to break the system and the
database doesn't delete or modify anything for you.<br>
<br>
No foreign key constraints then.<br>
<br>
Mark<br>
<pre class="moz-signature" cols="72">_____________________________________________
Mob: 07725 695178
Email: <a class="moz-txt-link-abbreviated" href="mailto:ma...@rw...">ma...@rw...</a></pre>
<br>
On 09/03/2011 16:00, David Thompson wrote:
<blockquote cite="mid:BAY...@ph..."
type="cite">
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style>
Scott, basically you're right, we would need to specify mySQL and
InnoDB engine for all TSNG installations, and what is the gain in
our case? We are like 90% of all LAMP projects, in that we use a
DB just for persistency and not for transactional safety or
integrity.<br>
<br>
The main relation we have is like a dependancy tree -
client<-project<-task<-entry - and restrictions will just
make it harder to delete something higher in that tree (clients
with projects cannot be deleted). An alternative to restrictions
is sensible defaults, when a client is deleted then his projects
get assigned to No_client(0). But since deletion is not a common
use case, we haven't <br>
<br>
The other main set of relations are to do with the users, and
admittedly this is not clean yet (user_id is defined but not
really used). Here there is the problem of deleting users, for
which, when we discussed it before, it seems that the best answer
is to not delete users, but set them as inactive.<br>
<br>
So my vote would be -1, never touch a running system.<br>
Sorry <br>
<hr id="stopSpelling">
Date: Wed, 9 Mar 2011 15:34:34 +0000<br>
From: <a class="moz-txt-link-abbreviated" href="mailto:sco...@gm...">sco...@gm...</a><br>
To: <a class="moz-txt-link-abbreviated" href="mailto:ma...@rw...">ma...@rw...</a><br>
CC: <a class="moz-txt-link-abbreviated" href="mailto:tsh...@li...">tsh...@li...</a><br>
Subject: Re: [Tsheetx-developers] Relational Database<br>
<br>
I know just enough DB theory to be dangerous here, so please
correct me where needed.<br>
<br>
My understanding is that for the Foreign key constraints to work,
we would need to write "triggers" so the database knows what do do
when certain events happen (deletion, addition, change of
records). I believe these "triggers" aren't exactly perfectly
portable between different databases, so we would have the need to
have different triggers written for whatever databases we decide
to support. <br>
<br>
Really our system is pretty darned simple from a database
perspective, so, my question would be, is it worth the development
time to tackle making the system truly relational? I'm currently
on the fence, but I'm leaning toward no :-) But, if you think
it's worth it, and want to do the heavy lifting, I'll certainly
support that decision.<br>
<br>
-Scott<br>
<br>
<div class="ecxgmail_quote">On Tue, Mar 8, 2011 at 11:57 PM, Mark
Wrightson <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:ma...@rw...">ma...@rw...</a>></span>
wrote:<br>
<blockquote style="padding-left: 1ex;" class="ecxgmail_quote">Hi
All<br>
<br>
I have recently started looking into InnoDB Foreign key
constraints to<br>
create truely relational databases. Has anyone considered
doing this<br>
for tsheetx? Is there a reason why we cannot or should not?<br>
<br>
I have recently found a tool in PHPMyAdmin called 'Designer'
that looks<br>
pretty cool, and shows the links between tables much like an
OO class<br>
diagram does.<br>
<br>
Thanks<br>
Mark Wrightson<br>
<br>
--<br>
_____________________________________________<br>
<br>
Mob: 07725 695178<br>
Email: <a moz-do-not-send="true"
href="mailto:ma...@rw...">ma...@rw...</a><br>
<br>
<br>
------------------------------------------------------------------------------<br>
Colocation vs. Managed Hosting<br>
A question and answer guide to determining the best fit<br>
for your organization - today and in the future.<br>
<a moz-do-not-send="true"
href="http://p.sf.net/sfu/internap-sfd2d" target="_blank">http://p.sf.net/sfu/internap-sfd2d</a><br>
_______________________________________________<br>
Tsheetx-developers mailing list<br>
<a moz-do-not-send="true"
href="mailto:Tsh...@li...">Tsh...@li...</a><br>
<a moz-do-not-send="true"
href="https://lists.sourceforge.net/lists/listinfo/tsheetx-developers"
target="_blank">https://lists.sourceforge.net/lists/listinfo/tsheetx-developers</a><br>
</blockquote>
</div>
<br>
<br>
------------------------------------------------------------------------------
Colocation vs. Managed Hosting A question and answer guide to
determining the best fit for your organization - today and in the
future. <a class="moz-txt-link-freetext" href="http://p.sf.net/sfu/internap-sfd2d">http://p.sf.net/sfu/internap-sfd2d</a><br>
_______________________________________________ Tsheetx-developers
mailing list <a class="moz-txt-link-abbreviated" href="mailto:Tsh...@li...">Tsh...@li...</a>
<a class="moz-txt-link-freetext" href="https://lists.sourceforge.net/lists/listinfo/tsheetx-developers">https://lists.sourceforge.net/lists/listinfo/tsheetx-developers</a> </blockquote>
</body>
</html>
|
