many tables or single table 
Author Message
 many tables or single table

Hi there,

Usually when I build my tables I also add a "notes" table for most of the
main tables. So if I had "customer" then I would have "customerNotes". Well,
I'm working on a new project and have thought about maybe making the Notes
tables generic, meaning just one "Notes" table, with an id to the
"TableNameTable" to see which table is attatched to and a generic "cId" for
the primary key for the record that is attatched to in a certain table.

Now first the positives, I don't have a lot of tables just one. Everything
is centralized. Negatives, more programming needed to handle and maintain
this relationship, can't do the built in referential integrity b/c the "cId"
field has to be char since tables can have an integer or character primary
key.

Anybody has done this? What are your thoughts about it? I'm beginning to
think that it might not be worth the work and trouble to put everything into
one table, even though I might have 14 note tables, it's easy to maintain.

Any thoughts?



Fri, 16 Sep 2005 21:37:23 GMT  
 many tables or single table
Hi Roy,

The setup you're describing sounds like the one used in TakeNote's FoxAudit.
(www.takenote.com). There's one audit table and adds/changes/deletes are all
written to that table. There's an autonumber PK which is basically a row
number, then there's a foreign key (character) which represents the PK of
the row in the table it comes from. In this case it's good to have one table
since FoxAudit has a mechanism to roll the whole database back in time,
meaning all the tables in the database need to be restored to the same
point. So, your idea would work.

But, do you really want to do this? With one table you could have one
generic routine to write to it, but then again you could have a generic
routine to write to each specific table.

Why aren't you just using a Memo field with each table?

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> Hi there,

> Usually when I build my tables I also add a "notes" table for most of the
> main tables. So if I had "customer" then I would have "customerNotes".
> Well,
> I'm working on a new project and have thought about maybe making the Notes
> tables generic, meaning just one "Notes" table, with an id to the
> "TableNameTable" to see which table is attatched to and a generic "cId"
> for
> the primary key for the record that is attatched to in a certain table.

> Now first the positives, I don't have a lot of tables just one. Everything
> is centralized. Negatives, more programming needed to handle and maintain
> this relationship, can't do the built in referential integrity b/c the
> "cId"
> field has to be char since tables can have an integer or character primary
> key.

> Anybody has done this? What are your thoughts about it? I'm beginning to
> think that it might not be worth the work and trouble to put everything
> into
> one table, even though I might have 14 note tables, it's easy to maintain.

> Any thoughts?



Fri, 16 Sep 2005 22:48:37 GMT  
 many tables or single table
Because it breaks normalization?  (I know, nit-picky.  I use the Memo
approach, myself.)

 - Rush


Quote:
> Hi Roy,

<snip>

> Why aren't you just using a Memo field with each table?

> --
> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP



Sat, 17 Sep 2005 00:24:26 GMT  
 many tables or single table
It only breaks normalization if it is designed to hold
multiple entries in a single memo field.  If it's just
one entry per record, it is normalized.  The original
poster did not indicate what the note-to-record relationship
is, so it could go either way.  

If it's mutliple entries
and these notes are an important aspect of the system,
I would use a separate notes table for each note-needing
table.  The complexity level of this arrangement is much
lower than a single notes table.  The single notes table
only *seems* simpler because it is one table.  That
simplicity is skin deep.

-- TRW


Quote:
> Because it breaks normalization?  (I know, nit-picky.  I use the Memo
> approach, myself.)

>  - Rush



>> Hi Roy,

> <snip>

>> Why aren't you just using a Memo field with each table?

>> --
>> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

_______________________________________
My e-mail:  t r w 7

_______________________________________


Sat, 17 Sep 2005 01:19:26 GMT  
 many tables or single table
Keep the note columns in the proper tables according to rules for normalized
database.  There is no advantage to keeping notes in a separate table, only
disadvantages.


Sat, 17 Sep 2005 02:29:06 GMT  
 many tables or single table
I would argue that it breaks normalization in that the Notes field is
typically optional, which makes it a very limited example of a repeating
field, in that a given record may require zero or one fields of type Note.
(I would probably lose the argument, but so be it).

Next case: suppose the Note is specific to a given field?  Now, every field
needing a Note requires its own Notes field.

More in my reply to Chaim.

 - Rush


Quote:
> It only breaks normalization if it is designed to hold
> multiple entries in a single memo field.  If it's just
> one entry per record, it is normalized.  The original
> poster did not indicate what the note-to-record relationship
> is, so it could go either way.

> If it's mutliple entries
> and these notes are an important aspect of the system,
> I would use a separate notes table for each note-needing
> table.  The complexity level of this arrangement is much
> lower than a single notes table.  The single notes table
> only *seems* simpler because it is one table.  That
> simplicity is skin deep.

> -- TRW


> > Because it breaks normalization?  (I know, nit-picky.  I use the Memo
> > approach, myself.)

> >  - Rush



> >> Hi Roy,

> > <snip>

> >> Why aren't you just using a Memo field with each table?

> >> --
> >> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> _______________________________________
> My e-mail:  t r w 7

> _______________________________________



Sat, 17 Sep 2005 03:28:06 GMT  
 many tables or single table
After my first reply, I began to reconsider - maybe it wasn't nit-picking.
after all.

No rules are carved in stone (except, of course, that grids aren't for data
entry), but if I violate a rule, I try to justify it.  Like as not, further
analysis causes me to follow the rule.

A Notes can apply either to the entire record or to a specific field.  If
you want to tie it to a field, you have to create a Notes field for each
'Notable' field.  Which means that when the client wants to add a Note to
another field, you have to restructure the table - a sure sign that your
design was flawed.

OTOH, if you store all your Notes in a single table (containing TableName,
FieldName, Note), you can add Notes functionality to any field in any table
by simply modifying your code.  In fact, you could include a 'Notable'
property in your base classes which would popup a Notes edit box based upon
a standard key stroke (Ctrl-N) or as a right-click menu option.

Advantage: single table.

Different notes from different tables or different fields might all relate
to a common subject - e.g., various notes might reference 'DevCon.'

If you wanted to find all such references, what are your options?
    - you can loop through every Notes field in every table; or
    - you can search a single field in a single table.

Advantage: single table.

Frankly, the more I look at it, the more I see my own design philosophy
changing over to the single table approach.

 - Rush


Quote:
> Keep the note columns in the proper tables according to rules for
normalized
> database.  There is no advantage to keeping notes in a separate table,
only
> disadvantages.



Sat, 17 Sep 2005 03:42:01 GMT  
 many tables or single table
By the way, I'm using SQL2000, but applies to foxpro tables as well.

Ok, here's an example of what I was referring to:
Have a "Customer" table (primary key nCust_id - integer)
Have a "User" table (primary key cUser_id - character)
Have a "Tables" table (primaky key nTable_id - integer)

Customer
nCust_id    cName
300            Mark Thomas
400            Mary Wells

User
cUser_id      cName
CDIXON    Carl Dixon
TSCOTT     Tammy Scott

nTable_id   cTableName
1                Customer
2                User

the Notes Table would be:
nNote_id    nTable_id        cId                    Note (memo or tex if sql
server)
1                1                    300                    This is a note
for customer Mark Thomas
2                1                    400                    This is a note
for customer Mary Wells
3                2                    CDIXON           This is a note for
user CDIXON
4                2                    TSCOTT            This is a note for
user TSCOTT
5                1                    300                    This is a
second note for user Mark Thomas

Now, it's nice to think about all the notes being in one table, and I have
done that so far but it's
kind of tedious to write extra code to handle the relationships when it's so
simple to program and
so visually logical to have "customer" and "customer_notes" even though it's
more tables. Ok,
so I said I'll still give it a try but then... I wanted to build my
referential integrity for the tables
but now it's broken b/c I can't tie a customer to it's notes for referential
integrity b/c both have
to be the same key type, so I have to write the referential integrity myself
and I can do that but
makes me think if it's worth all the trouble, I think I can handle having
15-20 extra tables b/c
it's easy to program with them and they all make sense.

Centralization is tempting, especially since no need to created future note
tables, just add an entry
to the "Tables" table but I'm leaning toward breaking them apart.


Quote:
> I would argue that it breaks normalization in that the Notes field is
> typically optional, which makes it a very limited example of a repeating
> field, in that a given record may require zero or one fields of type Note.
> (I would probably lose the argument, but so be it).

> Next case: suppose the Note is specific to a given field?  Now, every
field
> needing a Note requires its own Notes field.

> More in my reply to Chaim.

>  - Rush



> > It only breaks normalization if it is designed to hold
> > multiple entries in a single memo field.  If it's just
> > one entry per record, it is normalized.  The original
> > poster did not indicate what the note-to-record relationship
> > is, so it could go either way.

> > If it's mutliple entries
> > and these notes are an important aspect of the system,
> > I would use a separate notes table for each note-needing
> > table.  The complexity level of this arrangement is much
> > lower than a single notes table.  The single notes table
> > only *seems* simpler because it is one table.  That
> > simplicity is skin deep.

> > -- TRW

> > Rush Strong seemed to utter in


- Show quoted text -

Quote:

> > > Because it breaks normalization?  (I know, nit-picky.  I use the Memo
> > > approach, myself.)

> > >  - Rush



> > >> Hi Roy,

> > > <snip>

> > >> Why aren't you just using a Memo field with each table?

> > >> --
> > >> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> > _______________________________________
> > My e-mail:  t r w 7

> > _______________________________________



Sat, 17 Sep 2005 11:31:06 GMT  
 many tables or single table
Hi rguti!

Quote:
> Ok, here's an example of what I was referring to:
...
> the Notes Table would be:
> nNote_id    nTable_id        cId                    Note (memo or tex if
sql
> server)
> 1                1                    300                    This is a

note for customer Mark Thomas
...
Quote:
> 3                2                    CDIXON           This is a note for

user CDIXON
...
Quote:
> 5                1                    300                    This is a

second note for user Mark Thomas

Quote:
> but now it's broken b/c I can't tie a customer to it's notes for
referential
> integrity b/c both have
> to be the same key type, so I have to write the referential integrity
myself
> and I can do that but
> makes me think if it's worth all the trouble, I think I can handle having
> 15-20 extra tables b/c
> it's easy to program with them and they all make sense.

> Centralization is tempting, especially since no need to created future
note
> tables, just add an entry
> to the "Tables" table but I'm leaning toward breaking them apart.

It's not only the different primary key field types (number or char)
If you define refenrential integrity, then the foreign keys you store in
your central notes table would have to be checked against the (different)
referred tables.

So even if your keys where all integers, the normal RI wouldn't support
such multiple relations and you'd have to program your own RI code.

I'd stay with multiple notes tables. It's much more compatible if you
work with documentation wizards, UML, stonefield database toolkit, etc.
Alone the ERM of the tables within a dbc, that vfp itself draws from
the definined relations would miss the relations from notes to all other
tables.

Bye, Olaf.



Sun, 18 Sep 2005 04:56:40 GMT  
 many tables or single table
Hi,

We tend to use a single notes table with 2 fields
TABLENAME, DATAKEY to identify the parent table (DATAKEY
being the PK value in the parent).

We also use this concept for the Address and Contacts
tables. In the case of the Address table, the parent table
has 2 fields, PhyAddrid and PstAddrid, which contain
default value ID's. When a physical or postal address is
added, then these vaules are used in the Address table,
along with the TABLENAME of "ADDRESS".

Horses for courses I guess.

Cheers,

Nick

Quote:
>-----Original Message-----
>Hi there,

>Usually when I build my tables I also add a "notes" table
for most of the
>main tables. So if I had "customer" then I would

have "customerNotes". Well,
Quote:
>I'm working on a new project and have thought about maybe
making the Notes
>tables generic, meaning just one "Notes" table, with an
id to the
>"TableNameTable" to see which table is attatched to and a
generic "cId" for
>the primary key for the record that is attatched to in a
certain table.

>Now first the positives, I don't have a lot of tables

just one. Everything
Quote:
>is centralized. Negatives, more programming needed to
handle and maintain
>this relationship, can't do the built in referential

integrity b/c the "cId"
Quote:
>field has to be char since tables can have an integer or
character primary
>key.

>Anybody has done this? What are your thoughts about it?
I'm beginning to
>think that it might not be worth the work and trouble to
put everything into
>one table, even though I might have 14 note tables, it's
easy to maintain.

>Any thoughts?

>.



Sun, 18 Sep 2005 07:40:37 GMT  
 many tables or single table
You'll note that my comments were based on a particular
use of the field.  Just because a field has a certain
name does not dictate its use.

-- TRW

Rush Strong seemed to utter in

Quote:
> I would argue that it breaks normalization in that the Notes field is
> typically optional, which makes it a very limited example of a
> repeating field, in that a given record may require zero or one fields
> of type Note. (I would probably lose the argument, but so be it).

> Next case: suppose the Note is specific to a given field?  Now, every
> field needing a Note requires its own Notes field.

> More in my reply to Chaim.

>  - Rush



>> It only breaks normalization if it is designed to hold
>> multiple entries in a single memo field.  If it's just
>> one entry per record, it is normalized.  The original
>> poster did not indicate what the note-to-record relationship
>> is, so it could go either way.

>> If it's mutliple entries
>> and these notes are an important aspect of the system,
>> I would use a separate notes table for each note-needing
>> table.  The complexity level of this arrangement is much
>> lower than a single notes table.  The single notes table
>> only *seems* simpler because it is one table.  That
>> simplicity is skin deep.

>> -- TRW

>> Rush Strong seemed to utter in

>> > Because it breaks normalization?  (I know, nit-picky.  I use the
>> > Memo approach, myself.)

>> >  - Rush



>> >> Hi Roy,

>> > <snip>

>> >> Why aren't you just using a Memo field with each table?

>> >> --
>> >> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

>> _______________________________________
>> My e-mail:  t r w 7

>> _______________________________________

_______________________________________
My e-mail:  t r w 7

_______________________________________


Mon, 19 Sep 2005 00:46:57 GMT  
 many tables or single table
Rush Strong seemed to utter in

Quote:
> After my first reply, I began to reconsider - maybe it wasn't
> nit-picking. after all.

> No rules are carved in stone (except, of course, that grids aren't for
> data entry), but if I violate a rule, I try to justify it.  Like as
> not, further analysis causes me to follow the rule.

> A Notes can apply either to the entire record or to a specific field.
> If you want to tie it to a field, you have to create a Notes field for
> each 'Notable' field.  Which means that when the client wants to add a
> Note to another field, you have to restructure the table - a sure sign
> that your design was flawed.

> OTOH, if you store all your Notes in a single table (containing
> TableName, FieldName, Note), you can add Notes functionality to any
> field in any table by simply modifying your code.  In fact, you could
> include a 'Notable' property in your base classes which would popup a
> Notes edit box based upon a standard key stroke (Ctrl-N) or as a
> right-click menu option.

> Advantage: single table.

I have developed, maintained, used, and reviewed many, many
user interfaces for database systems.  I have never seen
one where there was a feature like this and I have never
had users or management request anything like this.  Not
to say it couldn't happen, but, IMHO, this is a poor
example to justify a single table.  This feature could
be supported easily with the one-notes-table-per-table
approach by adding a field_name field to the notes table.

Quote:
> Different notes from different tables or different fields might all
> relate to a common subject - e.g., various notes might reference
> 'DevCon.'

> If you wanted to find all such references, what are your options?
>     - you can loop through every Notes field in every table; or
>     - you can search a single field in a single table.

> Advantage: single table.

This is a more reasonable justification for the single
notes table.  But the advantage is minimal.  Write a
single, simple (searchAllNotes) method or procedure and
the multiple notes tables approach is again equivalent.

Coding ease: advantage multiple tables
Simplicity: advantage multiple tables
Normalization: advantage multiple tables
Referential Intergrity: advantage multiple tables

For these and other reasons, having the "many" side of
a one-to-many relationship related to many tables is
poor database design.  You will end up with the foreign
key in your notes table referring to multiple, different
primary keys in multiple tables.  After a quick examination,
you may think it is a great approach, but there are
many pitfalls and shortcomings that your quick
examination will not reveal.  Decades of database
design theory and experience available have been
shaped by confronting these problems and forming
the best practices to implement solutions.  And these
solutions are suprisingly simple (as the best solutions
tend to be).

We can learn from the experience of others or make
all of the mistakes on our own.

-- TRW
_______________________________________
My e-mail:  t r w 7

_______________________________________



Mon, 19 Sep 2005 01:26:06 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. Summing two tables into a single cross tab file

2. close a single table

3. Need help closing or deleteing a single table

4. Printing Multiple Tables in a single report

5. How do you close a single free table?

6. Appending data into a single master table.......Help!!!!!!!!!!

7. 2 Controls, single table, 1 form

8. change value in table depending on values in other table

9. Recalculating table with values from other table

10. Help -- matching data in lookup table, writing to target table

11. Looking to update a table from another table

12. TRYING TO UPDATE sql TABLES WITH FOX TABLES

 

 
Powered by phpBB® Forum Software