Le 08/03/2011 15:21, Tom Austin a écrit :
> A change for all of you database experts to shine....
> 
> I am working with a group to set up a new database system to handle our
> core work flow data.
> 
> We have a working model of the database but are considering some changes
> that affect the core of how it operates and I am seeking input as to
> some best/worst practices.
> 
> Any insight into the following issues/questions would be most greatly
> appreciated.
> 
> 
> 1.  Is the practice of creating separate databases for each 'job'
> considered good or bad?  Is this answer different based on using MS
> Access .mdb files vs using a MySQL Database engine?
> 

If the pieces of data are common or shared, even by the tiniest amount,
a single database is the answer.

Otherwise, how would you handle data's coherency ?

Access won't scale, drop it past the template step.
I won't enter holy wars about other SQL engines, but MySQL is rather
light (well, it get better every year).
You might really get benefit from engine which support views.


> 
> 2.  How is creating references across databases viewed?
>       Example - one database with definitions of data with many other
> databases referencing the definitions.

Go back to step 1;
You probably mean read-only views for a job.


> 
> 
> 3.  Normalizing vs Flattening?  At what point is a database 'too'
> normalized and does it matter?  Do you have some thoughts on joins using
> text data types instead of integer numbers?

Do not optimise before actual tests.

Normalise at least to 3rd form for your data, and keep it so!
The schema of the database should be checked up to 5th form, but that
usually transparent with good database people that are used to the task.

Conceiving the full schema (for all jobs) is needed before writing the
first line, or you will end up rewriting code when a job breaks a
relationship in many...

If you have performance issue, adding some index can help.
If you are normalised to 3++, you should not have blob of data to scan
to achieve your tasks.

Last point: any personal data (such as name of customer) might requires
the clearance of the local law authority (declaring the database, and
getting it allowed, obligation to allow query and modify on basis of
remote people)... For instance, the IP address is such personal piece,
even without name.



-- 
Software is like dirt - it costs time and money to change it and move it
around.

Just because you can't see it, it doesn't weigh anything,
and you can't drill a hole in it and stick a rivet into it doesn't mean
it's free.

Post a reply to this message