Where to start!

On May 5, 2008, at 6:21 AM, Tony Marston wrote:

>
>
>
> Anyone who doesn't know how to reach 3NF shouldn't be designing
> databases.

Just out of curiosity... How many Records do you need to have in a
database before you'll start seeing a performance boost from doing
that? I have written a few database apps that work quite well with no
formal training in PHP or Database design.. Don't want to start any
wars here... I'm just curious :)


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
japruim@raoset.com

>> Anyone who doesn't know how to reach 3NF shouldn't be designing
>> databases.
>
> Just out of curiosity... How many Records do you need to have in a
> database before you'll start seeing a performance boost from doing that?

There's no hard and fast rule. When it becomes a chore to maintain I
suppose (which you could say, is more than one...).

--
Richard Heyes

+----------------------------------------+
| Access SSH with a Windows mapped drive |
| http://www.phpguru.org/sftpdrive |
+----------------------------------------+

"Richard Heyes" <richardh@phpguru.org> wrote in message
news:481EF634.6010705@phpguru.org...
>> I do not agree that creating a database which is normalised to3NF is a
>> waste of time.
>
> It isn't always, but it is sometimes. When time is a (significant) factor,
> getting something up and running (which has acceptable performance) may be
> more impotant than creating a technically perfect solution. In fact
> creating something that is technically perfect is often just a pipe dream
> for programmers.

You obviously do not understand what "technically perfect" means when it
comes to data normalisation. 3NF is considered to be "good enough" while
4NF, 5NF and 6NF is considered to be "perfect". But there is such a thing as
"too perfect" which is why even C J Date (the father of relational theory)
said that de-normalsation is an acceptable practice. Every database designer
worth his salt should be able to achieve 3NF without breaking into a sweat,
while 4/5/6NF are entirely optional, and only worth it under paricular
circumstances.

> > On the contrary, a totally un-normalised database is nothing but a
>> problem waiting to bite you in the a**e.
>
> So you can:
>
> a) Create something that gets you to market as fast as possible that is
> "good enough".
> b) Optimise/adjust the structure later.
>
> IME though, b) rarely happens.

That's why solutions which are thrown together are often incapable of being
expanded to include new requirements. If you create a "throw away" solution
then never try to expand it, simply throw it away and start again.

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org

> > Computer systems have a habit of
>> growing over time
>
> Really?
>
>> ...and if you don't follow the rules of normalisation your database will
>> end up as the biggest bottleneck.
>
> Granted it's more likely, but not a given. You just need developers who
> have discipline, oh and a good memory helps.
>
>> Anyone who doesn't know how to reach 3NF shouldn't be designing
>> databases.
>
> Rubbish. It helps, in particular for how you can optimise you structure
> without duplicating data (too much), but shouldn't be a requirement.
>
> --
> Richard Heyes
>
> +----------------------------------------+
> | Access SSH with a Windows mapped drive |
> | http://www.phpguru.org/sftpdrive |
> +----------------------------------------+

"Jason Pruim" <japruim@raoset.com> wrote in message
news:36133942-D547-43E4-9521-8B8C1D80D206@raoset.com...
>
> On May 5, 2008, at 6:21 AM, Tony Marston wrote:
>
>>
>>
>>
>> Anyone who doesn't know how to reach 3NF shouldn't be designing
>> databases.
>
> Just out of curiosity... How many Records do you need to have in a
> database before you'll start seeing a performance boost from doing that?
> I have written a few database apps that work quite well with no formal
> training in PHP or Database design.. Don't want to start any wars here...
> I'm just curious :)

It is not the number of records which is the deciding factor - it is:
a) is the data want in the right place (for easy access)?
b) can it be upated easily?

For example, many years ago I had to take over maintenance of a database
which was designed b someone who was not technically competent. There were 2
paticular tables, "order_header" and "order_lines". The order had a status
value, as did each of the order lines. So where did this twit choose to
store the order status? - you've guessed it, on the order_line record. His
rationale was that when traversing the order_line recods it was easier to
have the order_status on that record instead of having to perform a separate
read of the order_header record. The flaw in this argument was that when you
wanted to change the status of an order you had to update every order_line
record. Problems arose later when a software bug (introduced by him, BTW)
left different values for order_status across different order_lines.

The rules of normalisation dictate that order_status goes on the
order_header record, and is not dupliacted across several order_line
records.

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org


> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424-9337
> www.raoset.com
> japruim@raoset.com
>
>
>

> You obviously do not understand what "technically perfect" means when it
> comes to data normalisation.

Obviously.

> That's why solutions which are thrown together are often incapable of being
> expanded to include new requirements.

I've never adovocated "throwing together" a solution. Merely that the
solutions proposed by programmers or database designers aren't always
necessary.

> If you create a "throw away" solution
> then never try to expand it, simply throw it away and start again.

For what reason? If you create a working solution and never try to
expand it, chances are you don't need to, so what's the advantage in
throwing it away and generating work for yourself in reproducing it?

--
Richard Heyes

+----------------------------------------+
| Access SSH with a Windows mapped drive |
| http://www.phpguru.org/sftpdrive |
+----------------------------------------+

On Mon, May 5, 2008 at 3:15 AM, Jorge <japreja@msn.com> wrote:

> First I would like to say that I have had PHP experience but not enough to
> say I am a PHP developer.
>
> I want to use PHP to build a site that uses MySQL, I am building it from
> scratch so I don't know where to start, should I start with PHP and design a
> database around my code, or should I designs the db and design the site are
> the db.
>
> is it just a matter of preference or is there a recommended way to do
> this?
>
>
I think while Richard and Tony are duking it out, maybe you could explain a
little more about this application. How big do you think it will be. How
much acitivity, etc? That would probably help us point you in the right
direction.

--
-Dan Joseph

www.canishosting.com - Plans start @ $1.99/month. Reseller plans and
Dedicated servers available.

"Build a man a fire, and he will be warm for the rest of the day.
Light a man on fire, and will be warm for the rest of his life."

On Mon, 2008-05-05 at 10:31 +0100, Tony Marston wrote:
> "Richard Heyes" <richardh@phpguru.org> wrote in message
> news:481ED03B.7090200@phpguru.org...
> > > The database must be properly normalised otherwise it
> >> will be difficult to get at the data you need in an efficient manner.
> >
> > Not true. If your needs are simple for example, normalisation can increase
> > the complexity of a schema, hence increasing development time needed.
> > Sometimes for example you could use something like a SET type, and search
> > for something in it using FIND_IN_SET().
>
> I disagree. The database should ALWAYS be normalised to at least the 3rd
> normal form. Sometimes going beyond that to 4NF, 5NF or 6NF becomes too
> complex.

I'd have to agree for the most part. There are times when you
specifically will include redundancy for speed, but as a newbie you
probably won't be thinking about that.

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

On Mon, 2008-05-05 at 11:10 +0100, Richard Heyes wrote:
> Tony Marston wrote:
> > "Richard Heyes" <richardh@phpguru.org> wrote in message
> > news:481ED03B.7090200@phpguru.org...
> >>> The database must be properly normalised otherwise it
> >>> will be difficult to get at the data you need in an efficient manner.
> >> Not true. If your needs are simple for example, normalisation can increase
> >> the complexity of a schema, hence increasing development time needed.
> >> Sometimes for example you could use something like a SET type, and search
> >> for something in it using FIND_IN_SET().
> >
> > I disagree. The database should ALWAYS be normalised to at least the 3rd
> > normal form. Sometimes going beyond that to 4NF, 5NF or 6NF becomes too
> > complex.
>
> Sometimes it's just a waste of time. If your site isn't that busy, and
> speed isn't crucial, why waste the time using a more complicated
> database structure?

Hmmm, my last post should have clarified I agreed primarily with 3rd
normal form. Higher levels don't usually have the same kind of payoff.

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

On Mon, 2008-05-05 at 13:51 +0100, Tony Marston wrote:
> "Richard Heyes" <richardh@phpguru.org> wrote in message
> news:481EF634.6010705@phpguru.org...
> >> I do not agree that creating a database which is normalised to3NF is a
> >> waste of time.
> >
> > It isn't always, but it is sometimes. When time is a (significant) factor,
> > getting something up and running (which has acceptable performance) may be
> > more impotant than creating a technically perfect solution. In fact
> > creating something that is technically perfect is often just a pipe dream
> > for programmers.
>
> You obviously do not understand what "technically perfect" means when it
> comes to data normalisation. 3NF is considered to be "good enough" while
> 4NF, 5NF and 6NF is considered to be "perfect".

Perfectly tedious ;)

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

On May 5, 2008, at 9:43 AM, Dan Joseph wrote:

> On Mon, May 5, 2008 at 3:15 AM, Jorge <japreja@msn.com> wrote:
>
>> First I would like to say that I have had PHP experience but not
>> enough to
>> say I am a PHP developer.
>>
>> I want to use PHP to build a site that uses MySQL, I am building
>> it from
>> scratch so I don't know where to start, should I start with PHP and
>> design a
>> database around my code, or should I designs the db and design the
>> site are
>> the db.
>>
>> is it just a matter of preference or is there a recommended way to do
>> this?
>>
>>
> I think while Richard and Tony are duking it out, maybe you could
> explain a
> little more about this application. How big do you think it will
> be. How
> much acitivity, etc? That would probably help us point you in the
> right
> direction.

Right now I just have 1 application, with a small account in it, it's
about 1,000 records... But I plan on marketing the program which would
(Hopefully) increase the user base and total records. I could see
having 20-30 customers with all with around 1,000 to 30,000+ names in
the database. At least that's what I'm hoping for.

We'll see how the marketing goes! :)


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
japruim@raoset.com