pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table Relationships
12+ messages / 7 participants
[nested] [flat]

* Table Relationships
@ 2003-05-30 09:23 Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 10:10 ` Re: Table Relationships Victor Yegorov <viktors.jegorovs@nordlb.lv>
  2003-05-30 12:11 ` Re: Table Relationships Bruno Wolff III <bruno@wolff.to>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  0 siblings, 3 replies; 12+ messages in thread

From: Jeandre du Toit @ 2003-05-30 09:23 UTC (permalink / raw)
  To: pgsql-performance


What are the advantages to having a database relational? I am currently 
discussing the design of a database with some people at work and they 
reckon it is best to create one table with and index and all the data 
instead of normalizing the database. I think that they think that joining 
tables will slow down retrieval, is this true?

Thanks
Jeandre




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
@ 2003-05-30 10:10 ` Victor Yegorov <viktors.jegorovs@nordlb.lv>
  2003-06-12 09:32   ` Re: Table Relationships Andreas Kostyrka <andreas@mtg.co.at>
  2 siblings, 1 reply; 12+ messages in thread

From: Victor Yegorov @ 2003-05-30 10:10 UTC (permalink / raw)
  To: Jeandre du Toit <jeandre@itvs.co.za>; +Cc: pgsql-performance

* Jeandre du Toit <jeandre@itvs.co.za> [30.05.2003 12:57]:
> 
> What are the advantages to having a database relational? I am currently 
> discussing the design of a database with some people at work and they 
> reckon it is best to create one table with and index and all the data 
> instead of normalizing the database. I think that they think that joining 
> tables will slow down retrieval, is this true?
> 

Take a look at situation from another side.

Let's say: You own a store and have 3 customers and 5 products on your
store. All you going to keep in DB is track of all purchases.

So, each time a customer will by a product, an new record will be added.
What this means:

1. Customer's name will be repeated as many times, as many purchases he had
   made. The same for each of products. In real world, you'll have about
   10,000 customers and about 100,000 products. Do you have enoght space on
   your disks to store all that stuff?

2. Some of your customers decided to change it's name. What you're going to
   do? If you're going to insert new purchases of that customer with he's new
   name, then in all turnover reports you'll have to specify both:
   old name and new one. If he will hange his name again - again, all
   reports are to be updated.

There is much more stuff to read about Relational Data Model in books.

About slowing down retrieval of data: all efforts today are put to speed up
things. You should think about your convenience in data manipulation.


I suggest you should try both: one huge table, and a set of normalized
tables and compare, what is quicker and what is easier to use.

-- 

Victor Yegorov



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 10:10 ` Re: Table Relationships Victor Yegorov <viktors.jegorovs@nordlb.lv>
@ 2003-06-12 09:32   ` Andreas Kostyrka <andreas@mtg.co.at>
  0 siblings, 0 replies; 12+ messages in thread

From: Andreas Kostyrka @ 2003-06-12 09:32 UTC (permalink / raw)
  To: Victor Yegorov <viktors.jegorovs@nordlb.lv>; +Cc: Jeandre du Toit <jeandre@itvs.co.za>; pgsql-performance

On Fri, 2003-05-30 at 12:10, Victor Yegorov wrote:
> Take a look at situation from another side.
> 
> Let's say: You own a store and have 3 customers and 5 products on your
> store. All you going to keep in DB is track of all purchases.
> 
> So, each time a customer will by a product, an new record will be added.
> What this means:
> 
> 1. Customer's name will be repeated as many times, as many purchases he had
>    made. The same for each of products. In real world, you'll have about
>    10,000 customers and about 100,000 products. Do you have enoght space on
>    your disks to store all that stuff?
Well, to play the devil's advocate, to do it correctly, you should
probably store the customer data duplicate (one in the main record, and
once in the purchase order). If you do not, you'll get an ERP system
that is incapable to reproduce work done, which is basically a BAD
THING(tm) :)


> 2. Some of your customers decided to change it's name. What you're going to
>    do? If you're going to insert new purchases of that customer with he's new
>    name, then in all turnover reports you'll have to specify both:
>    old name and new one. If he will hange his name again - again, all
>    reports are to be updated.
Well, again, a purchase order should keep records -> it shouldn't
magically change the name or address of the customer, just because the
customer moved.

Andreas

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
@ 2003-05-30 12:11 ` Bruno Wolff III <bruno@wolff.to>
  2003-05-30 13:34   ` Re: Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2 siblings, 1 reply; 12+ messages in thread

From: Bruno Wolff III @ 2003-05-30 12:11 UTC (permalink / raw)
  To: Jeandre du Toit <jeandre@itvs.co.za>; +Cc: pgsql-performance

On Fri, May 30, 2003 at 11:23:10 +0200,
  Jeandre du Toit <jeandre@itvs.co.za> wrote:
> 

Don't reply to existing threads to start a new thread.

> What are the advantages to having a database relational? I am currently 
> discussing the design of a database with some people at work and they 
> reckon it is best to create one table with and index and all the data 
> instead of normalizing the database. I think that they think that joining 
> tables will slow down retrieval, is this true?

You might want to read some books on relational database theory.

Date and Pascal are two noted authors of books on relational database theory.

> 
> Thanks
> Jeandre
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 12:11 ` Re: Table Relationships Bruno Wolff III <bruno@wolff.to>
@ 2003-05-30 13:34   ` Jeandre du Toit <jeandre@itvs.co.za>
  0 siblings, 0 replies; 12+ messages in thread

From: Jeandre du Toit @ 2003-05-30 13:34 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance

On Fri, 30 May 2003, Bruno Wolff III wrote:

> On Fri, May 30, 2003 at 11:23:10 +0200,
>   Jeandre du Toit <jeandre@itvs.co.za> wrote:
> > 
> 
> Don't reply to existing threads to start a new thread.

Sorry about that, I did something screwy in Pine. I thought that it would 
create a new mail.

> 
> > What are the advantages to having a database relational? I am currently 
> > discussing the design of a database with some people at work and they 
> > reckon it is best to create one table with and index and all the data 
> > instead of normalizing the database. I think that they think that joining 
> > tables will slow down retrieval, is this true?
> 
> You might want to read some books on relational database theory.
> 
> Date and Pascal are two noted authors of books on relational database theory.

Thanks, I will look at these books.

> 
> > 
> > Thanks
> > Jeandre
> > 
> > 




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
@ 2003-05-30 16:06 ` Josh Berkus <josh@agliodbs.com>
  2003-05-30 16:19   ` Re: Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:39   ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 2 replies; 12+ messages in thread

From: Josh Berkus @ 2003-05-30 16:06 UTC (permalink / raw)
  To: Jeandre du Toit <jeandre@itvs.co.za>; pgsql-performance

Jeandre,

> instead of normalizing the database. I think that they think that joining
> tables will slow down retrieval, is this true?

No, it's not.   I'm afraid that your co-workers learned their computer 
knowledge 10 years ago and have not kept up to date.  They may need 
retraining.

Modern database systems, especially PostgreSQL, are much faster with a proper 
relational schema than with an inadequate flat-file table, due to the 
efficient storage of data ... i.e., no redundancy.   

I highly suggest that you take a look at the book "Database Design for Mere 
Mortals"; if you're asking the question you posted, you are nowhere near 
ready to build a production database application.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
@ 2003-05-30 16:19   ` Jeandre du Toit <jeandre@itvs.co.za>
  1 sibling, 0 replies; 12+ messages in thread

From: Jeandre du Toit @ 2003-05-30 16:19 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance

On Fri, 30 May 2003, Josh Berkus wrote:

> Jeandre,
> 
> > instead of normalizing the database. I think that they think that joining
> > tables will slow down retrieval, is this true?
> 
> No, it's not.   I'm afraid that your co-workers learned their computer 
> knowledge 10 years ago and have not kept up to date.  They may need 
> retraining.

Thought as much

> 
> Modern database systems, especially PostgreSQL, are much faster with a proper 
> relational schema than with an inadequate flat-file table, due to the 
> efficient storage of data ... i.e., no redundancy.   

That is what I thought, but since they out rank me at work I needed the 
extra conformation. Now at least I can show them that I am not the only 
person that thinks a flat table structure is stone age design. I know for a 
fact it is better on Sybase, but I wasn't to sure about postgres and since 
they have been working on it for longer than I have, I am expected to 
follow their lead.

> 
> I highly suggest that you take a look at the book "Database Design for Mere 
> Mortals"; if you're asking the question you posted, you are nowhere near 
> ready to build a production database application.
> 

Thanks, I will have a look at that book. You are right, I am only first 
year Bsc, but I had a feeling that the facts they are giving me can't be 
right, it just didn't make any sense. They way I figured it, is that 
having a relational database, makes the database smaller because there is 
no duplicate data, which should make it faster.

Thanks for your help. I will approach my managers.
Jeandre
 




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
@ 2003-05-30 16:39   ` Andrew Sullivan <andrew@libertyrms.info>
  2003-05-30 17:03     ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Andrew Sullivan @ 2003-05-30 16:39 UTC (permalink / raw)
  To: pgsql-performance

On Fri, May 30, 2003 at 09:06:43AM -0700, Josh Berkus wrote:
> Modern database systems, especially PostgreSQL, are much faster with a proper 
> relational schema than with an inadequate flat-file table, due to the 
> efficient storage of data ... i.e., no redundancy.   

Are you sure you want to say it that strongly?  After all, if you
have a data set which needs always to be returned in the same static
format, why not just denormalise it?  It's sure faster that way in
every system I've ever encountered.

It's only when you actually have relations to cope with that it
ceases to be an advantage.  So, as usual, it depends on what you're
trying to do.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  2003-05-30 16:39   ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
@ 2003-05-30 17:03     ` Josh Berkus <josh@agliodbs.com>
  2003-05-30 17:20       ` Re: Table Relationships scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Josh Berkus @ 2003-05-30 17:03 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

Andrew,

> Are you sure you want to say it that strongly?  After all, if you
> have a data set which needs always to be returned in the same static
> format, why not just denormalise it?  It's sure faster that way in
> every system I've ever encountered.
>
> It's only when you actually have relations to cope with that it
> ceases to be an advantage.  So, as usual, it depends on what you're
> trying to do.

Yeah, I suppose so ... if all they're doing is reporting on a static set of 
data which is not transactional ... sure.  If it's a disposable, 
limited-time-use application.

However, I have yet to see in my professional experience any application that 
was really this way and stayed this way once it was in use ... relations have 
a way of creeping in, and planning for them is less messy than refactoring.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  2003-05-30 16:39   ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
  2003-05-30 17:03     ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
@ 2003-05-30 17:20       ` scott.marlowe <scott.marlowe@ihs.com>
  2003-05-30 18:18         ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 1 reply; 12+ messages in thread

From: scott.marlowe @ 2003-05-30 17:20 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

On Fri, 30 May 2003, Josh Berkus wrote:

> Andrew,
> 
> > Are you sure you want to say it that strongly?  After all, if you
> > have a data set which needs always to be returned in the same static
> > format, why not just denormalise it?  It's sure faster that way in
> > every system I've ever encountered.
> >
> > It's only when you actually have relations to cope with that it
> > ceases to be an advantage.  So, as usual, it depends on what you're
> > trying to do.
> 
> Yeah, I suppose so ... if all they're doing is reporting on a static set of 
> data which is not transactional ... sure.  If it's a disposable, 
> limited-time-use application.
> 
> However, I have yet to see in my professional experience any application that 
> was really this way and stayed this way once it was in use ... relations have 
> a way of creeping in, and planning for them is less messy than refactoring.

My philosophy has been you store the data normalized, and denormalize it 
for performance down the line.

but denormalizing for storage is usually a bad idea, as it allows your 
data to get filled with inconsistencies.

It's funny how people start worrying about performance of flat versus 
normalized before really looking at the difference between the two first.  
On Postgresql and most other databases, there are far more important 
concerns to worry about when it comes to performance than whether or not 
you're joining a couple tables.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  2003-05-30 16:39   ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
  2003-05-30 17:03     ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  2003-05-30 17:20       ` Re: Table Relationships scott.marlowe <scott.marlowe@ihs.com>
@ 2003-05-30 18:18         ` Andrew Sullivan <andrew@libertyrms.info>
  2003-05-30 19:54           ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Andrew Sullivan @ 2003-05-30 18:18 UTC (permalink / raw)
  To: pgsql-performance

On Fri, May 30, 2003 at 11:20:33AM -0600, scott.marlowe wrote:

> but denormalizing for storage is usually a bad idea, as it allows your 
> data to get filled with inconsistencies.

Sure, but if performance is an important goal for certain kinds of
SELECTs, using a trigger at insert or update to do denormalising is
perhaps an acceptable approach.  It's obvious that in most cases,
denormalising instead of optimising your normalisation is silly.  But
if you need something to return in, say, 2ms most of the time, and it
requires a wide variety of data, denormalising is a good idea.

It is, of course, contrary to the RDBMS-y mind to denormalise.  But
there are (rare) times when it's a good idea, and I hate to see it
rejected out of hand in such cases.

A
-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Table Relationships
  2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
  2003-05-30 16:06 ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  2003-05-30 16:39   ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
  2003-05-30 17:03     ` Re: Table Relationships Josh Berkus <josh@agliodbs.com>
  2003-05-30 17:20       ` Re: Table Relationships scott.marlowe <scott.marlowe@ihs.com>
  2003-05-30 18:18         ` Re: Table Relationships Andrew Sullivan <andrew@libertyrms.info>
@ 2003-05-30 19:54           ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Josh Berkus @ 2003-05-30 19:54 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

Andrew,

> Sure, but if performance is an important goal for certain kinds of
> SELECTs, using a trigger at insert or update to do denormalising is
> perhaps an acceptable approach.  It's obvious that in most cases,
> denormalising instead of optimising your normalisation is silly.  But
> if you need something to return in, say, 2ms most of the time, and it
> requires a wide variety of data, denormalising is a good idea.

I've done this plenty of times ... but what you're talking about is more of a 
"materialized view" than denormalized data.   The data is still stored in 
normal form; it is just distilled for a particular view and saved on disk for 
quick reference.  This is often a good approach with performance-sensitive, 
complex databases.

> It is, of course, contrary to the RDBMS-y mind to denormalise.  But
> there are (rare) times when it's a good idea, and I hate to see it
> rejected out of hand in such cases.

There is a big difference between denormalizing normalized data and storing 
your data in denormalized (basically flat file) form in the first place.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2003-06-12 09:32 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-30 09:23 Table Relationships Jeandre du Toit <jeandre@itvs.co.za>
2003-05-30 10:10 ` Victor Yegorov <viktors.jegorovs@nordlb.lv>
2003-06-12 09:32   ` Andreas Kostyrka <andreas@mtg.co.at>
2003-05-30 12:11 ` Bruno Wolff III <bruno@wolff.to>
2003-05-30 13:34   ` Jeandre du Toit <jeandre@itvs.co.za>
2003-05-30 16:06 ` Josh Berkus <josh@agliodbs.com>
2003-05-30 16:19   ` Jeandre du Toit <jeandre@itvs.co.za>
2003-05-30 16:39   ` Andrew Sullivan <andrew@libertyrms.info>
2003-05-30 17:03     ` Josh Berkus <josh@agliodbs.com>
2003-05-30 17:20       ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-30 18:18         ` Andrew Sullivan <andrew@libertyrms.info>
2003-05-30 19:54           ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox