Re: speed

From: windandwaves (winandwaves_at_coldmail.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 12:36:10 +1300

"Henk Verhoeven" <news1@phppeanutsREMOVE-THIS.org> wrote in message news:cvb64q$12e$1@news3.zwoll1.ov.home.nl...
> Hi Nicolaas
>
> It is easy to put all data in one table, but it is even easier to put a lot of errorneous data in one table. Soon you (or your end
> users) will experience 'database pollution'. When the table holds repeated data (if you do no databas desing it usually does) the
> pollution gets really irritating as things that somehow belong together can in practice often not be found 'together' in the
> database.
>
> To make a long story short, a well designed database is a good foundation for proper application that helps the users to maintain
> the data and prevent database pollution. So i suggest speed should not be your only design criterium. But IF your only concern is
> speed: why don't you just try it out?
>
> Greetings,
>
> Henk Verhoeven,
> www.phpPeanuts.org
>
> P.S. The aim of well designed code is very similar to a well designed database: make maintenance easier (i mean maintenance of the
> code). So are the means: avoid repetition. So learning about database design may in time bring you more then 'just' statisfied end
> users ;-)
>
> windandwaves wrote:
>
>> Hi Folks
>>
>> I am putting together a couple of tables in PHP/MySql that are rather large:
>>
>> 137 fields with at least about seventy medium text field.
>>
>> The table only contains about 500 rows and will never grow substantially (well, it would be nice (they are members).
>>
>> I could have created a dozen or so tables, which would have been a lot sexier from a database design perspective, but as I am a
>> PHP newby, I did not like that idea.
>>
>> Should I be worried about speed? and if so, how I can I measure speed.
>>
>> I know it all depends.
>>
>> I use a standard ISP package (not a dedicated server). I expect only a handful users of the website at the same time. These
>> large tables are read by a page that displays all the information (address, characteristics, prices, etc...) from all 136 fields.
>> No difficult queries are used at all.
>>
>> I would love to hear any experiences that you may have had that can help me.
>>
>> - Nicolaas

Maintainabilility is my main concern. In that way I designed the database. However, I was worried that I may perhaps give away
too much speed.

I totally understand your point above. I usually make supersleek databases with not a dot of repeated info. However, you can go too
far in normalisation.

In this particular instance. We are talking about 500 B&Bs, each with their own pieces of information. For example, each B&B has a
list of about 15 facilities. About 10-20% of them are repeated, so I could put it in a table like this:

Main table
 unique facilities list
 table with link between main table and facilities

That would be the proper way to do it. But all the extra SQL, in my opinion, in this instant, with only 10-20% repeat, does not
weigh up against the joy of having the fields F1 to F25 in the main table.

Search wise it is obviously a little harder to search 25 fields, but I simply make one field with all the words in it everytime the
user updates their data (this fields is called Keywords) and then search that. I know it is really ugly database design, but it
allows me to achieve my goal (finish the website!).

Cheers

- Nicolaas

Relevant Pages

  • Re: Relationships. Does anyone use them?
    ... If you do not care to "go in into the relationships window to set the relationship" you are, quite simply, going to fail badly any relational database course. ... Relationships are the constraints that are needed to be set up to ensure database structural integrity. ... You really need to go over this in detail, because right now I can tell you, no offence intended and with the greatest of respect, that you are NOT DOING RELATIONAL DATABASE DESIGN WORK at present. ...
    (comp.databases.ms-access)
  • Re: Table design - reducing number of entities
    ... I've looked at a lot of crappy code written against databases. ... lot of that code is crappy, because the database design itself was crappy. ... > look at all the constraints on the putative entities--not forgetting the ...
    (comp.databases.theory)
  • Re: Relationships. Does anyone use them?
    ... why do I need to add 2 tables in the Relationship window and set relationships? ... If you do not care to "go in into the relationships window to set the relationship" you are, quite simply, going to fail badly any relational database course. ... When a database application gets large and widely used, the following is an example of what happens that simply screams for the lowest level constraints to be used. ... You really need to go over this in detail, because right now I can tell you, no offence intended and with the greatest of respect, that you are NOT DOING RELATIONAL DATABASE DESIGN WORK at present. ...
    (comp.databases.ms-access)
  • Re: Basic Database Design
    ... the Client has a Insurance Company ... you'll need some further knowledge on the subject of database design. ... Books: General: Beginner ...
    (microsoft.public.access.tablesdbdesign)
  • Re: mailmerge in Word 2002 fails
    ... words, it will start Access, get it to open the database, then get the data ... using workgroup security) connection will be more difficult. ... > essentially disable the capability without notifying developers? ... facilities" have been there from an ...
    (microsoft.public.word.mailmerge.fields)