RE: Database design question

[Doug Bezona]

> Ok, I think I am understanding. So are you saying that I will need
another
> table if this is the case? Say I have antiques and collectible and it
has
> a
> sub_category of furniture. Since not all furniture is antique or
> collectible
> would that require another table?

Exactly. Instead of defining the parent-child relationship in the
category table, you use a second related table to define the
relationship, which allows for a subcategory to have a relationship to
multiple parents. For example:

[categories]
Cat_id
Category


[categoryRelationships]
Cat_id [FK_categories]
Parentid [FK_categories]

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250575
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Doug Bezona]

> >As someone previously stated, the major drawback to this
> >design is that you can only have one parent for each item.  If you
need
> >an item to have more than one parent, you would have to develop a two
> >table design.
> 
> I can't imagine a scenario where I would need to have a single child
have
> two parent records...but if I DID need that, I believe I might just go
> ahead and clone my child (create two child records with the same
category
> description), giving each one the appropriate parent ID. Hopefully
this
> would be the rare exception to the rule...that way I could stick to my
one
> table design and not have to, as was pointed out, get myself into the
> situation where i could potentially have to be creating X number of
tables
> to support my relationships.

I can actually think of several situations. For example, one content
management system I developed had this requirement - it supported
multiple sites that often had certain content pages in common. 

Allowing a child to have multiple parents allowed for pieces of content
to live in more than one place without having to dupe the record, so if
the content was edited, the changes were reflected everywhere the page
appeared. 



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250574
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Database design question

[Doug Brown]

Hey Doug,

Doug again here...I implemented the database that you and others were
speaking of. Everything seems to be in order with the exception of one
thing. I am populating a menu with the categories from the table and when
there is no more records as I drill down, the menu will simply dissapear.
This problem did not present itself in the old design due to the fact I was
using 2 tables and joing them. I suppose there was always records there that
way. Any suggestions to stop the menu from vanishing when there is no
records to build it with?

Thanks

Doug
- Original Message - 
From: "Doug Boude (rhymes with 'loud')" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Monday, August 21, 2006 3:33 PM
Subject: Re: Database design question

> >
> >
> >
> >- Original Message - 
> >From: "Michael E. Carluen" <[EMAIL PROTECTED]>
> >To: "CF-Talk" 
> >Sent: Monday, August 21, 2006 12:53 PM
> >Subject: RE: Database design question
> >
> >
> >> Doug, yYou can actually use a single table for that. One way is to
> >create
> >a
> >relationships
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250572
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Database design question

[rhymes with 'loud']

Wow, look at all the Doug B's here! 

Awhile back I posted some code samples on my blog regarding this very topic, 
though in my example I was working with a hierarchical navigation system where 
nav items could have children who could have children, etc. The concept has 
been quite adequately explained, but if you're looking for some code snippets 
and such, here they be: 
http://www.dougboude.com/blog/index.cfm?mode=archive&year=2006&month=6&day=17

>As someone previously stated, the major drawback to this
>design is that you can only have one parent for each item.  If you need
>an item to have more than one parent, you would have to develop a two
>table design.

I can't imagine a scenario where I would need to have a single child have two 
parent records...but if I DID need that, I believe I might just go ahead and 
clone my child (create two child records with the same category description), 
giving each one the appropriate parent ID. Hopefully this would be the rare 
exception to the rule...that way I could stick to my one table design and not 
have to, as was pointed out, get myself into the situation where i could 
potentially have to be creating X number of tables to support my relationships.

two more cents for the pot.

>
>Hope that clears things up a bit.
>
>Rich Kroll
>
>-Original Message-
>From: Doug Brown [mailto:[EMAIL PROTECTED] 
>Sent: Monday, August 21, 2006 3:26 PM
>To: CF-Talk
>Subject: Re: Database design question
>
>Thanks michael, but if I do it that way, will I not have hundreds of
>tables?
>I currently have about 24 categories and each category has prob 10-50
>sub_categories and then each sub_category has it's own set of
>sub_categories.
>
>
>
>- Original Message - 
>From: "Michael E. Carluen" <[EMAIL PROTECTED]>
>To: "CF-Talk" 
>Sent: Monday, August 21, 2006 12:53 PM
>Subject: RE: Database design question
>
>
>> Doug, yYou can actually use a single table for that. One way is to
>create
>a
>relationships

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250521
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Database design question

[Doug Brown]

Ok, I think I am understanding. So are you saying that I will need another
table if this is the case? Say I have antiques and collectible and it has a
sub_category of furniture. Since not all furniture is antique or collectible
would that require another table?



- Original Message - 
From: "Doug Bezona" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Monday, August 21, 2006 12:43 PM
Subject: RE: Database design question


> This is going to be a bit tricky to explain without diagrams, but I'll
> try and hope it's reasonably clear.
>
> One approach is use a single category table that is self-referential.
>
> For example, a table named category, with a categoryid, and a parentid.
> Parentid would be a foreign key relationship to categoryid.
>
> Using your example categories, let's say "Antiques" is categoryid = 1,
> with parentid = NULL. Antique Furniture would be, say, categoryid = 2,
> with parentid = 1, indicating its parent category is "Antiques" and so
> on.
>
> The only downside here is that if you ever decide you want a subcategory
> to be attached to multiple parent categories, you are kind of out of
> luck. In that case, you can use a slightly different model, in this case
> with two tables:
>
> 1. A Category table with your categoryid
> 2. A CategoryRelationship table with a categoryid which is the id of a
> given category record (category), and a parentid which represents the
> parent record (subcategory).  This allows you to have the same
> parent-child hierarchy as the first example, but a given category can
> have multiple parents, with each record in the table defining a
> particular relationship.
>
> There are other ways to approach hierarchical data like this, and it's
> probably worth Googling around a bit with that in mind. Working with
> hierarchical data like this can be tricky depending on how comfortable
> you are with SQL, and what RDBMS you are using (Oracle has some great
> proprietary methods for dealing with hierarchies - other databases, not
> so much)
>
> What you do absolutely want to avoid is having separate category, sub
> category, sub-sub category, etc. tables.
>
> Doug B.
>
>
> -Original Message-
> From: Doug Brown [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 2:16 PM
> To: CF-Talk
> Subject: Database design question
>
> On my classifieds database it will have...
>
> categories and corresponding sub_categories and corresponding
> sub_sub_categories. How would you design the table names and
> relationships to avoid confusion? Kinda new to database design!!
>
> IE:
>
> Antiques-category
> antique furniture - sub_category
> hutches - sub_sub_category
>
>
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250516
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: Database design question

[Michael E. Carluen]

No, only one table.  I believe Doug Bezona and I are suggesting an identical
approach (so you now have a consensus solution).


> -Original Message-
> From: Doug Brown [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 12:26 PM
> To: CF-Talk
> Subject: Re: Database design question
> 
> Thanks michael, but if I do it that way, will I not have hundreds of
> tables?
> I currently have about 24 categories and each category has prob 10-50
> sub_categories and then each sub_category has it's own set of
> sub_categories.
> 
> 
> 
> - Original Message -
> From: "Michael E. Carluen" <[EMAIL PROTECTED]>
> To: "CF-Talk" 
> Sent: Monday, August 21, 2006 12:53 PM
> Subject: RE: Database design question
> 
> 
> > Doug, yYou can actually use a single table for that. One way is to
> create
> a
> > field that serves as a "parent_id".
> >
> > Example:
> >
> > ID: 1, NAME: Antiques and Vintages, PARENT_ID: 0
> > ID: 2, NAME: Antique Furniture, PARENT_ID: 1
> > ID: 3, NAME: Vintage Cars, PARENT_ID: 1
> > ID: 4, NAME: Hutches, PARENT_ID: 2
> > ID: 5, NAME: DeSoto, PARENT_ID: 3
> >
> > Hope that makes sense, Doug.
> >
> > Michael
> >
> >


~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250505
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Doug Bezona]

To simplify what I was getting at in my previous reply, what I was
suggesting is this single table (in place of the three you are
suggesting):

[categories]
cat_id
category
parent_id [FK_categories]

This method allows for unlimited levels of subcategories using only a
single table. 

The top level category would have a null value for the parent_id (it has
no parents)

The parent_id of any subcategories would be the cat_id of the category
of which it is a sub. 

A couple of simple usage examples:

To get all top-level categories:

Select * from categories
Where parent_id is NULL

To get all direct subcategories of a particular category:

Select * from categories
Where parent_id = 1 (assuming 1 is the cat_id of the parent category in
question)

Doug B.

-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 21, 2006 3:23 PM
To: CF-Talk
Subject: Re: Database design question

Doug thanks for the help. By the way I am Doug too.

Right now I have this

[categories]
cat_id
category

[sub_categories]
sub_cat_id
cat_id [FK sub_categories_categories]
sub_categories

I am also going to be needing to add a table for the sub_categories of
the
sub_categories table. Hopefully this makes more sense. Would it be
something
like so

[categories]
cat_id
category

[sub_categories]
sub_cat_id
cat_id [FK sub_categories_categories]
sub_category

[sub_cat_categories]
sub_cat_sub_id
sub_cat_id [FK sub_cat_categories]
cat_id [FK sub_cat_categories_categories]
sub_cat_category


Thanks for the help again.





- Original Message - 
From: "Doug Bezona" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Monday, August 21, 2006 12:43 PM
Subject: RE: Database design question


> This is going to be a bit tricky to explain without diagrams, but I'll
> try and hope it's reasonably clear.
>
> One approach is use a single category table that is self-referential.
>
> For example, a table named category, with a categoryid, and a
parentid.
> Parentid would be a foreign key relationship to categoryid.
>
> Using your example categories, let's say "Antiques" is categoryid = 1,
> with parentid = NULL. Antique Furniture would be, say, categoryid = 2,
> with parentid = 1, indicating its parent category is "Antiques" and so
> on.
>
> The only downside here is that if you ever decide you want a
subcategory
> to be attached to multiple parent categories, you are kind of out of
> luck. In that case, you can use a slightly different model, in this
case
> with two tables:
>
> 1. A Category table with your categoryid
> 2. A CategoryRelationship table with a categoryid which is the id of a
> given category record (category), and a parentid which represents the
> parent record (subcategory).  This allows you to have the same
> parent-child hierarchy as the first example, but a given category can
> have multiple parents, with each record in the table defining a
> particular relationship.
>
> There are other ways to approach hierarchical data like this, and it's
> probably worth Googling around a bit with that in mind. Working with
> hierarchical data like this can be tricky depending on how comfortable
> you are with SQL, and what RDBMS you are using (Oracle has some great
> proprietary methods for dealing with hierarchies - other databases,
not
> so much)
>
> What you do absolutely want to avoid is having separate category, sub
> category, sub-sub category, etc. tables.
>
> Doug B.
>
>
> -Original Message-
> From: Doug Brown [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 2:16 PM
> To: CF-Talk
> Subject: Database design question
>
> On my classifieds database it will have...
>
> categories and corresponding sub_categories and corresponding
> sub_sub_categories. How would you design the table names and
> relationships to avoid confusion? Kinda new to database design!!
>
> IE:
>
> Antiques-category
> antique furniture - sub_category
> hutches - sub_sub_category
>
>
>
> 



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250501
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Richard Kroll]

The one table design that Michael is describing will hold all your
categories and sub-categories, as well as any further nesting that you
might need.

For example:

Cat_id, category_name,  parent_id
1,  category 1, NULL
2,  category 2, NULL

25, sub-category 1, 1
26, sub-category 2, 1

49, sub-category 49,2

200,sub-sub category 1, 2

Etc.

This single table design lets you nest sub-categories as deep as you
need to go.  As someone previously stated, the major drawback to this
design is that you can only have one parent for each item.  If you need
an item to have more than one parent, you would have to develop a two
table design.

Hope that clears things up a bit.

Rich Kroll

-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 21, 2006 3:26 PM
To: CF-Talk
Subject: Re: Database design question

Thanks michael, but if I do it that way, will I not have hundreds of
tables?
I currently have about 24 categories and each category has prob 10-50
sub_categories and then each sub_category has it's own set of
sub_categories.



- Original Message - 
From: "Michael E. Carluen" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Monday, August 21, 2006 12:53 PM
Subject: RE: Database design question


> Doug, yYou can actually use a single table for that. One way is to
create
a
> field that serves as a "parent_id".
>
> Example:
>
> ID: 1, NAME: Antiques and Vintages, PARENT_ID: 0
> ID: 2, NAME: Antique Furniture, PARENT_ID: 1
> ID: 3, NAME: Vintage Cars, PARENT_ID: 1
> ID: 4, NAME: Hutches, PARENT_ID: 2
> ID: 5, NAME: DeSoto, PARENT_ID: 3
>
> Hope that makes sense, Doug.
>
> Michael
>
>
> > -Original Message-
> > From: Doug Brown [mailto:[EMAIL PROTECTED]
> > Sent: Monday, August 21, 2006 11:16 AM
> > To: CF-Talk
> > Subject: Database design question
> >
> > On my classifieds database it will have...
> >
> > categories and corresponding sub_categories and corresponding
> > sub_sub_categories. How would you design the table names and
relationships
> > to avoid confusion? Kinda new to database design!!
> >
> > IE:
> >
> > Antiques-category
> > antique furniture - sub_category
> > hutches - sub_sub_category
> >
>
>
> 



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250500
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Database design question

[Doug Brown]

Thanks michael, but if I do it that way, will I not have hundreds of tables?
I currently have about 24 categories and each category has prob 10-50
sub_categories and then each sub_category has it's own set of
sub_categories.



- Original Message - 
From: "Michael E. Carluen" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Monday, August 21, 2006 12:53 PM
Subject: RE: Database design question


> Doug, yYou can actually use a single table for that. One way is to create
a
> field that serves as a "parent_id".
>
> Example:
>
> ID: 1, NAME: Antiques and Vintages, PARENT_ID: 0
> ID: 2, NAME: Antique Furniture, PARENT_ID: 1
> ID: 3, NAME: Vintage Cars, PARENT_ID: 1
> ID: 4, NAME: Hutches, PARENT_ID: 2
> ID: 5, NAME: DeSoto, PARENT_ID: 3
>
> Hope that makes sense, Doug.
>
> Michael
>
>
> > -Original Message-
> > From: Doug Brown [mailto:[EMAIL PROTECTED]
> > Sent: Monday, August 21, 2006 11:16 AM
> > To: CF-Talk
> > Subject: Database design question
> >
> > On my classifieds database it will have...
> >
> > categories and corresponding sub_categories and corresponding
> > sub_sub_categories. How would you design the table names and
relationships
> > to avoid confusion? Kinda new to database design!!
> >
> > IE:
> >
> > Antiques-category
> > antique furniture - sub_category
> > hutches - sub_sub_category
> >
>
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250495
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Database design question

[Doug Brown]

Doug thanks for the help. By the way I am Doug too.

Right now I have this

[categories]
cat_id
category

[sub_categories]
sub_cat_id
cat_id [FK sub_categories_categories]
sub_categories

I am also going to be needing to add a table for the sub_categories of the
sub_categories table. Hopefully this makes more sense. Would it be something
like so

[categories]
cat_id
category

[sub_categories]
sub_cat_id
cat_id [FK sub_categories_categories]
sub_category

[sub_cat_categories]
sub_cat_sub_id
sub_cat_id [FK sub_cat_categories]
cat_id [FK sub_cat_categories_categories]
sub_cat_category


Thanks for the help again.





- Original Message - 
From: "Doug Bezona" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Monday, August 21, 2006 12:43 PM
Subject: RE: Database design question


> This is going to be a bit tricky to explain without diagrams, but I'll
> try and hope it's reasonably clear.
>
> One approach is use a single category table that is self-referential.
>
> For example, a table named category, with a categoryid, and a parentid.
> Parentid would be a foreign key relationship to categoryid.
>
> Using your example categories, let's say "Antiques" is categoryid = 1,
> with parentid = NULL. Antique Furniture would be, say, categoryid = 2,
> with parentid = 1, indicating its parent category is "Antiques" and so
> on.
>
> The only downside here is that if you ever decide you want a subcategory
> to be attached to multiple parent categories, you are kind of out of
> luck. In that case, you can use a slightly different model, in this case
> with two tables:
>
> 1. A Category table with your categoryid
> 2. A CategoryRelationship table with a categoryid which is the id of a
> given category record (category), and a parentid which represents the
> parent record (subcategory).  This allows you to have the same
> parent-child hierarchy as the first example, but a given category can
> have multiple parents, with each record in the table defining a
> particular relationship.
>
> There are other ways to approach hierarchical data like this, and it's
> probably worth Googling around a bit with that in mind. Working with
> hierarchical data like this can be tricky depending on how comfortable
> you are with SQL, and what RDBMS you are using (Oracle has some great
> proprietary methods for dealing with hierarchies - other databases, not
> so much)
>
> What you do absolutely want to avoid is having separate category, sub
> category, sub-sub category, etc. tables.
>
> Doug B.
>
>
> -Original Message-
> From: Doug Brown [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 2:16 PM
> To: CF-Talk
> Subject: Database design question
>
> On my classifieds database it will have...
>
> categories and corresponding sub_categories and corresponding
> sub_sub_categories. How would you design the table names and
> relationships to avoid confusion? Kinda new to database design!!
>
> IE:
>
> Antiques-category
> antique furniture - sub_category
> hutches - sub_sub_category
>
>
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250494
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Michael E. Carluen]

Doug, yYou can actually use a single table for that. One way is to create a
field that serves as a "parent_id".

Example:

ID: 1, NAME: Antiques and Vintages, PARENT_ID: 0
ID: 2, NAME: Antique Furniture, PARENT_ID: 1
ID: 3, NAME: Vintage Cars, PARENT_ID: 1
ID: 4, NAME: Hutches, PARENT_ID: 2
ID: 5, NAME: DeSoto, PARENT_ID: 3

Hope that makes sense, Doug.

Michael


> -Original Message-
> From: Doug Brown [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 11:16 AM
> To: CF-Talk
> Subject: Database design question
> 
> On my classifieds database it will have...
> 
> categories and corresponding sub_categories and corresponding
> sub_sub_categories. How would you design the table names and relationships
> to avoid confusion? Kinda new to database design!!
> 
> IE:
> 
> Antiques-category
> antique furniture - sub_category
> hutches - sub_sub_category
> 


~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250492
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Doug Bezona]

This is going to be a bit tricky to explain without diagrams, but I'll
try and hope it's reasonably clear.

One approach is use a single category table that is self-referential.

For example, a table named category, with a categoryid, and a parentid.
Parentid would be a foreign key relationship to categoryid. 

Using your example categories, let's say "Antiques" is categoryid = 1,
with parentid = NULL. Antique Furniture would be, say, categoryid = 2,
with parentid = 1, indicating its parent category is "Antiques" and so
on.

The only downside here is that if you ever decide you want a subcategory
to be attached to multiple parent categories, you are kind of out of
luck. In that case, you can use a slightly different model, in this case
with two tables:

1. A Category table with your categoryid
2. A CategoryRelationship table with a categoryid which is the id of a
given category record (category), and a parentid which represents the
parent record (subcategory).  This allows you to have the same
parent-child hierarchy as the first example, but a given category can
have multiple parents, with each record in the table defining a
particular relationship.

There are other ways to approach hierarchical data like this, and it's
probably worth Googling around a bit with that in mind. Working with
hierarchical data like this can be tricky depending on how comfortable
you are with SQL, and what RDBMS you are using (Oracle has some great
proprietary methods for dealing with hierarchies - other databases, not
so much)

What you do absolutely want to avoid is having separate category, sub
category, sub-sub category, etc. tables. 

Doug B.


-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 21, 2006 2:16 PM
To: CF-Talk
Subject: Database design question

On my classifieds database it will have...

categories and corresponding sub_categories and corresponding
sub_sub_categories. How would you design the table names and
relationships to avoid confusion? Kinda new to database design!!

IE:

Antiques-category
antique furniture - sub_category
hutches - sub_sub_category



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250491
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Tim Laureska]

Hey Doug... I know this isn't a direct answer to your question, but
something for the future - - get this book... it made everything pretty
clear for relational databases:

Database Design for Mere Mortals . . by Michael Hernandez

-Original Message-
From: Doug Brown [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 21, 2006 2:16 PM
To: CF-Talk
Subject: Database design question

On my classifieds database it will have...

categories and corresponding sub_categories and corresponding
sub_sub_categories. How would you design the table names and relationships
to avoid confusion? Kinda new to database design!!

IE:

Antiques-category
antique furniture - sub_category
hutches - sub_sub_category



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250488
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Database design question

[Doug Brown]

On my classifieds database it will have...

categories and corresponding sub_categories and corresponding 
sub_sub_categories. How would you design the table names and relationships to 
avoid confusion? Kinda new to database design!!

IE:

Antiques-category
antique furniture - sub_category
hutches - sub_sub_category

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250484
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Database design question

[Paul Giesenhagen]

To me the table structure looks fine, it would be one line per classified 
ad.

If they can place more than one ad at a time, it wouldn't matter to the 
database.  You would write your scripts to allow for multiple instances of 
ads and then submit them through a loop, inserting them one at a time.

Works much like a shopping cart, you have one table for the order and a 
related table that holds all the products associated with that order. 
Sometimes orders have many products in them and you just loop through 
inserting each product into the productOrder table.

I hope this helps .. looks like you are fine!

Paul Giesenhagen
QuillDesign
417-885-1375
http://www.quilldesign.com


- Original Message - 
From: "Doug Brown" <[EMAIL PROTECTED]>
To: "CF-Talk" 
Sent: Wednesday, August 16, 2006 10:06 PM
Subject: Database design question


>I am creating a classifieds e-commerce site and was wondering...I have 
> the following columns that need to be in the database, and was wondering 
> if you would split them up into seperate tables. I am thinking no, but not 
> sure.
>
>I was considering letting customers place more than one ad at a time 
> and was thinking if I should use a structure to hold the info or if I 
> should create a temp_table that gets cleared out say once a week or more. 
> What would be better performance wise?
>
> (ads)
> ad_id
> cust_id
> cat_id
> sub_cat_id
> ad_text
> qty_on_hand
> ship_method
> item_condition
> unit_type
> refund_policy
> is_negotiable
> ad_title
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250107
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Database design question

[Doug Brown]

I am creating a classifieds e-commerce site and was wondering...I have the 
following columns that need to be in the database, and was wondering if you 
would split them up into seperate tables. I am thinking no, but not sure. 

I was considering letting customers place more than one ad at a time and 
was thinking if I should use a structure to hold the info or if I should create 
a temp_table that gets cleared out say once a week or more. What would be 
better performance wise?

(ads)
ad_id
cust_id
cat_id
sub_cat_id
ad_text
qty_on_hand
ship_method
item_condition
unit_type
refund_policy
is_negotiable
ad_title

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250106
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Database design question

[Ben Rogers]

> But what if you need read/write access? How transactional would
> that be?

In the SAP example cited, write access is not allowed. By that, I mean that
granting users (besides those used by the SAP/R3 system) write access to the
database voids your support contract. How's that for side stepping the
question? :)

I know of no practical problems as far as transactions go. If an error
occurs in the linked server, it bubbles up and rolls back the transaction in
SQL Server. I do not know how developers generally handle manual rollbacks.
They could be managed programmatically -- if not gracefully.

I can understand why cross-database and cross-server queries are not
supported -- it's that whole durability thing, right? :) It would probably
be difficult, if not impossible, to guarantee that you could restore the two
databases back to the same point. In fact, unless the system was bought down
for backups, you're database backups would be out of sync.

This brings me back to my original point. Most of what I've seen done is, as
I said, read only. The data in most of the databases is wiped and restored
regularly, or it's used for reporting purposes. We only care about the
durability of the application database, and, in general, we're only backing
up the application database.

Ben Rogers
http://www.c4.net
v.508.240.0051
f.508.240.0057


~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185017
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Jochem van Dieten]

Ben Rogers wrote:
> 
> I assume this goes for cross-server queries as well (such as linked servers
> in SQL Server)?

Of course implementations differ, but in general I consider 
cross-database and cross-server queries something you use because 
you *have* to, not because you *want* to.


> Well, sometimes it's just a heck of a lot easier. For instance, I'm working
> on several apps that either deal with data migration or collaborative
> creation. The SAP system (running either Oracle or SQL Server) is set up as
> a linked server or, sometimes, is just another database on the same server.
> Views are then built in the application database that query the SAP system
> (read only of course) to build lists of vendors, customers, etc.

But what if you need read/write access? How transactional would 
that be?

Jochem

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184989
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Aaron Rouse]

I do use a database link on my current project to another server
sitting in the same server room.  Unfortunately the columns that we
need to query on are not indexed on that box and to pull out records
takes for ever.  If it did not it would be nice to just write the
queries to join our tables off theirs.  Instead the solution so far
has been to run some SPs nightly that copy over the records that we
need to query into temp tables in our schema.

-- 
Aaron Rouse
http://www.happyhacker.com/

On Mon, 22 Nov 2004 08:24:58 -0500, Ben Rogers <[EMAIL PROTECTED]> wrote:

> 
> Well, sometimes it's just a heck of a lot easier. For instance, I'm working
> on several apps that either deal with data migration or collaborative
> creation. The SAP system (running either Oracle or SQL Server) is set up as
> a linked server or, sometimes, is just another database on the same server.
> Views are then built in the application database that query the SAP system
> (read only of course) to build lists of vendors, customers, etc.
>

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184987
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Database design question

[Ben Rogers]

> In theory joining cross databases is not supported in the SQL
> standard. Therefore not every dbms will implement it, or they
> will require you to use clumsy workarounds.

I assume this goes for cross-server queries as well (such as linked servers
in SQL Server)?

> I find it hard to see the advantages when you can also use
> namespaces (schema's) for logical separation and tablespaces for
> physical separation (other diskdrive). Unless your database is
> *really* big, but then replication/clustering might be a better
> solution.

Well, sometimes it's just a heck of a lot easier. For instance, I'm working
on several apps that either deal with data migration or collaborative
creation. The SAP system (running either Oracle or SQL Server) is set up as
a linked server or, sometimes, is just another database on the same server.
Views are then built in the application database that query the SAP system
(read only of course) to build lists of vendors, customers, etc.

Now, we could import the data nightly into the application database. In
fact, these apps can work that way if necessary. However, they import the
data into its own database (as opposed to a name space). The reason this is
done is because the data, which is usually many gigs in size, doesn't need
to be backed up. The application database, which is relatively small, is
backed up using standard database maintenance plans.

Ben Rogers
http://www.c4.net
v.508.240.0051
f.508.240.0057


~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184985
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Jochem van Dieten]

Scott Mulholland wrote:
> In theory is there any downside to joining cross database.

In theory joining cross databases is not supported in the SQL 
standard. Therefore not every dbms will implement it, or they 
will require you to use clumsy workarounds.

I find it hard to see the advantages when you can also use 
namespaces (schema's) for logical separation and tablespaces for 
physical separation (other diskdrive). Unless your database is 
*really* big, but then replication/clustering might be a better 
solution.

Jochem

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184963
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Database design question

[Dawson, Michael]

Oops.  I meant to say "there are many reasons why you _can't_ limit
everything to a single database". 

-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED] 
Sent: Saturday, November 20, 2004 9:46 PM
To: CF-Talk
Subject: RE: Database design question

I do it all the time, but I don't really like it.  However, there are
many reasons why you can limit everything to a single database.

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184961
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Database design question

[Dawson, Michael]

I do it all the time, but I don't really like it.  However, there are
many reasons why you can limit everything to a single database.

When I write a SQL statement, I ALWAYS use fully-qualified table names
such as:
databaseName.dbo.tableName

I do this even if I'm in the default database specified by the DSN.
This way, I never have to worry about what database is the default.

The only problems I ran across are:
1. If two databases contain views, or stored procs, that reference each
other at the same time.  You may have a problem when you try to script
these objects to move to a different server.

2. ER applications, such as ER/Studio, may not like, or support,
multi-database ERDs.  I know ER/Studio should support it in the future.
Right now, the work-around is to replace "." with "_" such as
databaseName_dbo_tableName.  Then, right before creating the objects,
you generate the DDL and replace the "_" with ".".  Kind of a hack, but
I guess it works.

M!ke

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 19, 2004 9:03 AM
To: CF-Talk
Subject: Database design question

In theory is there any downside to joining cross database.  I'm using
sql server and was considering having my user data in one database and
my content data in an other.  In some cases I would need to do something
like this as an example:
 
2 databases (MS SQL Server for arguments sake): USERS and CONTENT
 
datasource=CONTENT
select a.title, u.firstname, u.lastname
from articles a INNER JOIN USERS.dbo.accounts u ON a.insertby =
u.account_id
 
Is there any performance hit or other reasons to avoid having them as
separate databases?
 
Thanks,
Scott

---
[This E-mail scanned for viruses by Declude Virus]




~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184959
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Database design question

[Scott Mulholland]

Yeah, that's what im trying to figure out, the benefits of doing it this
way.  It is not for a central users database shared by applications so
that benefit is lost.  I just wanted to know if there was some best
practice out there for this.  Aside from having these 2 different types
of data seperated for organization I can't find any benefit to having
them be 2 databases.

-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 19, 2004 11:22 AM
To: CF-Talk
Subject: Re: Database design question


Well if you need multiple users data - again this sounds like the sort
of data that could be read once and cached, but you know your
application and how dynamic the data is...

The real question is what benefits do you get from having the user data
in a separate database, because it will undoubtably add complications of
one sort or another.


On Fri, 19 Nov 2004 10:38:27 -0500, Scott Mulholland
<[EMAIL PROTECTED]> wrote:
> Well some user data would be stored in session vars, like the 
> account_id and such, but my example was more along the lines of if you

> wanted to pull a list of articles to display the article and name of 
> the person who inserted it, whereas I take your query to give me the 
> list of articles inserted by a specific user.  Am I misinterpretting 
> it?
> 
> 
> 
> -Original Message-
> From: Michael Traher [mailto:[EMAIL PROTECTED]
> Sent: Friday, November 19, 2004 10:21 AM
> To: CF-Talk
> Subject: Re: Database design question
> 
> In reality would you not tend to only need to read the user data at 
> login time and then cache whatever user data is required (in a 
> persistant scope of some type) so that your example would become
> 
> select a.title, u.firstname, u.lastname
> from articles a
> WHERE a.insertby = #request.account_id#
> 
> aside from that I'm sure some MSSQL experts will answer your question 
> more specifically :-)
> 
> On Fri, 19 Nov 2004 10:02:45 -0500, Scott Mulholland 
> <[EMAIL PROTECTED]> wrote:
> > In theory is there any downside to joining cross database.  I'm 
> > using sql server and was considering having my user data in one 
> > database and
> 
> > my content data in an other.  In some cases I would need to do 
> > something like this as an example:
> >
> > 2 databases (MS SQL Server for arguments sake): USERS and CONTENT
> >
> > datasource=CONTENT
> > select a.title, u.firstname, u.lastname
> > from articles a INNER JOIN USERS.dbo.accounts u
> > ON a.insertby = u.account_id
> >
> > Is there any performance hit or other reasons to avoid having them 
> > as separate databases?
> >
> > Thanks,
> > Scott
> >
> > ---
> > [This E-mail scanned for viruses by Declude Virus]
> >
> >
> 
> 



~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184893
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Aaron Rouse]

If you had multiple projects that all had their own databases but need
a central database for user authentication.  We had something like
this years ago when I was working for the University.  The IT
department did finally get a LDAP solution working correctly and that
is when we switched to that instead.

-- 
Aaron Rouse
http://www.happyhacker.com/

On Fri, 19 Nov 2004 16:22:23 +, Michael Traher
<[EMAIL PROTECTED]> wrote:

> The real question is what benefits do you get from having the user
> data in a separate database, because it will undoubtably add
> complications of one sort or another.
>

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184891
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Michael Traher]

Well if you need multiple users data - again this sounds like the sort
of data that could be read once and cached, but you know your
application and how dynamic the data is...

The real question is what benefits do you get from having the user
data in a separate database, because it will undoubtably add
complications of one sort or another.


On Fri, 19 Nov 2004 10:38:27 -0500, Scott Mulholland
<[EMAIL PROTECTED]> wrote:
> Well some user data would be stored in session vars, like the account_id
> and such, but my example was more along the lines of if you wanted to
> pull a list of articles to display the article and name of the person
> who inserted it, whereas I take your query to give me the list of
> articles inserted by a specific user.  Am I misinterpretting it?
> 
> 
> 
> -Original Message-
> From: Michael Traher [mailto:[EMAIL PROTECTED]
> Sent: Friday, November 19, 2004 10:21 AM
> To: CF-Talk
> Subject: Re: Database design question
> 
> In reality would you not tend to only need to read the user data at
> login time and then cache whatever user data is required (in a
> persistant scope of some type) so that your example would become
> 
> select a.title, u.firstname, u.lastname
> from articles a
> WHERE a.insertby = #request.account_id#
> 
> aside from that I'm sure some MSSQL experts will answer your question
> more specifically :-)
> 
> On Fri, 19 Nov 2004 10:02:45 -0500, Scott Mulholland
> <[EMAIL PROTECTED]> wrote:
> > In theory is there any downside to joining cross database.  I'm using
> > sql server and was considering having my user data in one database and
> 
> > my content data in an other.  In some cases I would need to do
> > something like this as an example:
> >
> > 2 databases (MS SQL Server for arguments sake): USERS and CONTENT
> >
> > datasource=CONTENT
> > select a.title, u.firstname, u.lastname
> > from articles a INNER JOIN USERS.dbo.accounts u
> > ON a.insertby = u.account_id
> >
> > Is there any performance hit or other reasons to avoid having them as
> > separate databases?
> >
> > Thanks,
> > Scott
> >
> > ---
> > [This E-mail scanned for viruses by Declude Virus]
> >
> >
> 
> 

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184887
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[S . Isaac Dealey]

> In reality would you not tend to only need to read the
> user data at
> login time and then cache whatever user data is required
> (in a
> persistant scope of some type) so that your example would
> become

Not necessarily... if there is an administrative tool to manage users,
it might need to cross-reference some other data, or particularly if
the application needs to generate reports based on users (which might
include the user's name or simply an aggregated number of users in a
particular area at a particular time), then the database would need to
reference both the user database and the alternate database.

I'm not personally aware of any significant performance issues
involved with doing things this way either with mssql, oracle or any
other database. I would be surprised if there are any (unless you're
talking about using a table from a physically separate linked server
in another room or half-way around the world in which case you'll have
attenuation/lag issues).

The only issue I might expect from this is regarding migration and/or
replication -- if you ever need to migrate your database to another
machine, etc. you then have to move the user database first before
moving any other databases, and if there are constraints between
databases they may need to be dropped before migrating.



s. isaac dealey   954.927.5117

new epoch : isn't it time for a change?

add features without fixtures with
the onTap open source framework
http://www.sys-con.com/story/?storyid=44477&DE=1
http://www.sys-con.com/story/?storyid=45569&DE=1
http://www.fusiontap.com




~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184877
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Database design question

[Scott Mulholland]

Well some user data would be stored in session vars, like the account_id
and such, but my example was more along the lines of if you wanted to
pull a list of articles to display the article and name of the person
who inserted it, whereas I take your query to give me the list of
articles inserted by a specific user.  Am I misinterpretting it?

-Original Message-
From: Michael Traher [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 19, 2004 10:21 AM
To: CF-Talk
Subject: Re: Database design question


In reality would you not tend to only need to read the user data at
login time and then cache whatever user data is required (in a
persistant scope of some type) so that your example would become


select a.title, u.firstname, u.lastname
from articles a 
WHERE a.insertby = #request.account_id#

aside from that I'm sure some MSSQL experts will answer your question
more specifically :-)




On Fri, 19 Nov 2004 10:02:45 -0500, Scott Mulholland
<[EMAIL PROTECTED]> wrote:
> In theory is there any downside to joining cross database.  I'm using 
> sql server and was considering having my user data in one database and

> my content data in an other.  In some cases I would need to do 
> something like this as an example:
> 
> 2 databases (MS SQL Server for arguments sake): USERS and CONTENT
> 
> datasource=CONTENT
> select a.title, u.firstname, u.lastname
> from articles a INNER JOIN USERS.dbo.accounts u
> ON a.insertby = u.account_id
> 
> Is there any performance hit or other reasons to avoid having them as 
> separate databases?
> 
> Thanks,
> Scott
> 
> ---
> [This E-mail scanned for viruses by Declude Virus]
> 
> 



~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184871
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Michael Traher]

In reality would you not tend to only need to read the user data at
login time and then cache whatever user data is required (in a
persistant scope of some type) so that your example would become


select a.title, u.firstname, u.lastname
from articles a 
WHERE a.insertby = #request.account_id#

aside from that I'm sure some MSSQL experts will answer your question
more specifically :-)




On Fri, 19 Nov 2004 10:02:45 -0500, Scott Mulholland
<[EMAIL PROTECTED]> wrote:
> In theory is there any downside to joining cross database.  I'm using
> sql server and was considering having my user data in one database and
> my content data in an other.  In some cases I would need to do something
> like this as an example:
> 
> 2 databases (MS SQL Server for arguments sake): USERS and CONTENT
> 
> datasource=CONTENT
> select a.title, u.firstname, u.lastname
> from articles a INNER JOIN USERS.dbo.accounts u
> ON a.insertby = u.account_id
> 
> Is there any performance hit or other reasons to avoid having them as
> separate databases?
> 
> Thanks,
> Scott
> 
> ---
> [This E-mail scanned for viruses by Declude Virus]
> 
> 

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184869
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Database design question

[Scott Mulholland]

In theory is there any downside to joining cross database.  I'm using
sql server and was considering having my user data in one database and
my content data in an other.  In some cases I would need to do something
like this as an example:
 
2 databases (MS SQL Server for arguments sake): USERS and CONTENT
 
datasource=CONTENT
select a.title, u.firstname, u.lastname
from articles a INNER JOIN USERS.dbo.accounts u
ON a.insertby = u.account_id
 
Is there any performance hit or other reasons to avoid having them as
separate databases?
 
Thanks,
Scott

---
[This E-mail scanned for viruses by Declude Virus]


~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184863
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Database design question

[Dina Hess]

To refine a bit...

STUDENT(student_id [PK], student_name, ...)
GOAL(goal_id [PK], student_id [FK], goal_name, ...)
GRADE(grade_id [PK], goal_id [FK], grading_period, grade)

This reflects the one-to-many relationships---

** One student has many goals
** One goal has many grades

~Dina


- Original Message - 
  From: Bruce, Rodney S HQISEC/Veridian IT Services 
  To: CF-Talk 
  Sent: Thursday, November 21, 2002 3:34 PM
  Subject: RE: Database design question


  Janine

  one question:  would the goals be the same for all the students?

  I would go with 3 tables

  1.  Student info
  2. Goal info
  3.  link table with fk from student/goal tables and comments(grades) 

  something like

  Table1

  Studentid  StudentName 


  Table2
  Goalid GoalName 


  Table3
  Studentid  goalid   period(date)  Comments(grades)


  just my .02

   

  -Original Message-
  From: Janine Jakim [mailto:[EMAIL PROTECTED]]
  Sent: Thursday, November 21, 2002 1:59 PM
  To: CF-Talk
  Subject: OT: Database design question


  hmmm- I am very into keepting my database relational, but am wondering what
  the best route for thi particular issue is...
  Heres' the scoop 
  1.  One student can be working on many goals so I have 1 one to many
  relationships.
  2.  Each goal needs graded every marking period (up to 6 times a year)-
  these are not traditioanl grades but a long comments piece) So goals have a
  one to many relationship with the grades.
  I'm thinking the best way to handle it is to break it into 3 tables
  table one student info 
  table 2 all the goals for the students hooked to students with the studentid
  table 3 all the grades for the goals hooked to goals by goalID

  My other option is to have 2 tables
  table one student info
  table two goals +  6 grading periods comment sections
  Thanks in advance for any input.
  j




 

  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Re: OT: Database design question

[Jeffry Houser]

At 03:59 PM 11/21/2002 -0500, you wrote:
>hmmm- I am very into keepting my database relational, but am wondering what
>the best route for thi particular issue is...
>Heres' the scoop
>1.  One student can be working on many goals so I have 1 one to many
>relationships.

  I suspect this is a many to many relationship.

Student Table  (studentID, etc.. )
Goal Table (goalID, etc. )
Student_Goal Table (StudentGoalID, StudentID, GoalID )

  Unless a goal can only be associated with a single student.


>2.  Each goal needs graded every marking period (up to 6 times a year)-
>these are not traditioanl grades but a long comments piece) So goals have a
>one to many relationship with the grades.

I would probably do something like this:
MarkingPeriod Table
Grades  ( GradeID, StudentGoalID, MarkingPeriodID, Comment )



>I'm thinking the best way to handle it is to break it into 3 tables
>table one student info
>table 2 all the goals for the students hooked to students with the studentid
>table 3 all the grades for the goals hooked to goals by goalID

   This is fine, assuming that a goal is truly unique to a student.


--
Jeffry Houser | mailto:[EMAIL PROTECTED]
DotComIt, Putting you on the web
AIM: Reboog711  | Phone: 1-203-379-0773
--
My CFMX Book: 

My Books: http://www.instantcoldfusion.com
My Band: http://www.farcryfly.com 

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

RE: Database design question

[Bruce, Rodney S HQISEC/Veridian IT Services]

Janine

one question:  would the goals be the same for all the students?

I would go with 3 tables

1.  Student info
2. Goal info
3.  link table with fk from student/goal tables and comments(grades) 

something like

Table1

Studentid  StudentName 


Table2
Goalid  GoalName 


Table3
Studentid  goalid   period(date)  Comments(grades)


just my .02

 

-Original Message-
From: Janine Jakim [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 21, 2002 1:59 PM
To: CF-Talk
Subject: OT: Database design question


hmmm- I am very into keepting my database relational, but am wondering what
the best route for thi particular issue is...
Heres' the scoop 
1.  One student can be working on many goals so I have 1 one to many
relationships.
2.  Each goal needs graded every marking period (up to 6 times a year)-
these are not traditioanl grades but a long comments piece) So goals have a
one to many relationship with the grades.
I'm thinking the best way to handle it is to break it into 3 tables
table one student info 
table 2 all the goals for the students hooked to students with the studentid
table 3 all the grades for the goals hooked to goals by goalID

My other option is to have 2 tables
table one student info
table two goals +  6 grading periods comment sections
Thanks in advance for any input.
j




   

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

RE: OT: Database design question

[Janine Jakim]

No it's individual students with individual goals.
Thanks- I figured it was the best way, but it's getting cold and rainy here
and I think my brain is starting to hibernate for the winter...
j

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 21, 2002 4:10 PM
To: CF-Talk
Subject: Re: OT: Database design question


Janine Jakim wrote:

> 1.  One student can be working on many goals so I have 1 one to many
> relationships.
> 2.  Each goal needs graded every marking period (up to 6 times a 
> year)- these are not traditioanl grades but a long comments piece) So 
> goals have a one to many relationship with the grades.
> I'm thinking the best way to handle it is to break it into 3 tables
> table one student info table 2 all the goals for the students hooked 
> to students with the studentid table 3 all the grades for the goals 
> hooked to goals by goalID

Will students be cooperating on these goals and be graded together? If 
so, you actually have a many-to-many relationship. If not, your model is OK.

Jochem


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

RE: Database design question

[Ben Doom]

I would probably go for the 3-table layout.

First, if the goals are more typically graded 1 or 2 times per year, it
doesn't leave you with lots of wasted space for storing those grades,
comments, whatever.

Second, there's always the possibility that someone might need more -- for
example, a single goal graded six times each for two different departments.
Or maybe they add summer to it.  Who knows.

Finally, it seems to me that this gives you more flexibility and power when
it comes to slicing and dicing the data for analysis or display.

Of course, I'm far from a SQL expert, but that's my $.02



  --Ben Doom
Programmer & General Lackey
Moonbow Software

: -Original Message-
: From: Janine Jakim [mailto:[EMAIL PROTECTED]]
: Sent: Thursday, November 21, 2002 3:59 PM
: To: CF-Talk
: Subject: OT: Database design question
:
:
: hmmm- I am very into keepting my database relational, but am
: wondering what
: the best route for thi particular issue is...
: Heres' the scoop
: 1.  One student can be working on many goals so I have 1 one to many
: relationships.
: 2.  Each goal needs graded every marking period (up to 6 times a year)-
: these are not traditioanl grades but a long comments piece) So
: goals have a
: one to many relationship with the grades.
: I'm thinking the best way to handle it is to break it into 3 tables
: table one student info
: table 2 all the goals for the students hooked to students with
: the studentid
: table 3 all the grades for the goals hooked to goals by goalID
:
: My other option is to have 2 tables
: table one student info
: table two goals +  6 grading periods comment sections
: Thanks in advance for any input.
: j
:
:
:
:
:
: 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Re: OT: Database design question

[Matt Robertson]

Ack.  Always keep one-to-many stuff relational on general principles.  Its not that 
much extra work.

---
Matt Robertson, MSB Designs, Inc.
http://mysecretbase.com - Retail
http://foohbar.org - ColdFusion Tools
---


-- Original Message --
from: Janine Jakim <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
date: Thu, 21 Nov 2002 15:59:20 -0500

>hmmm- I am very into keepting my database relational, but am wondering what
>the best route for thi particular issue is...
>Heres' the scoop 
>1.  One student can be working on many goals so I have 1 one to many
>relationships.
>2.  Each goal needs graded every marking period (up to 6 times a year)-
>these are not traditioanl grades but a long comments piece) So goals have a
>one to many relationship with the grades.
>I'm thinking the best way to handle it is to break it into 3 tables
>table one student info 
>table 2 all the goals for the students hooked to students with the studentid
>table 3 all the grades for the goals hooked to goals by goalID
>
>My other option is to have 2 tables
>table one student info
>table two goals +  6 grading periods comment sections
>Thanks in advance for any input.
>j
>
>
>
>
>   
>
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Re: OT: Database design question

[Jochem van Dieten]

Janine Jakim wrote:

> 1.  One student can be working on many goals so I have 1 one to many
> relationships.
> 2.  Each goal needs graded every marking period (up to 6 times a 
> year)- these are not traditioanl grades but a long comments piece) So 
> goals have a one to many relationship with the grades.
> I'm thinking the best way to handle it is to break it into 3 tables
> table one student info table 2 all the goals for the students hooked 
> to students with the studentid table 3 all the grades for the goals 
> hooked to goals by goalID

Will students be cooperating on these goals and be graded together? If 
so, you actually have a many-to-many relationship. If not, your model is OK.

Jochem

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

OT: Database design question

[Janine Jakim]

hmmm- I am very into keepting my database relational, but am wondering what
the best route for thi particular issue is...
Heres' the scoop 
1.  One student can be working on many goals so I have 1 one to many
relationships.
2.  Each goal needs graded every marking period (up to 6 times a year)-
these are not traditioanl grades but a long comments piece) So goals have a
one to many relationship with the grades.
I'm thinking the best way to handle it is to break it into 3 tables
table one student info 
table 2 all the goals for the students hooked to students with the studentid
table 3 all the grades for the goals hooked to goals by goalID

My other option is to have 2 tables
table one student info
table two goals +  6 grading periods comment sections
Thanks in advance for any input.
j




   
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Re: database design question re:product price changes

[nagraj]

Hello Kola Oyedeji,


Oops..It was Order detail Table.rather than order table

Regards
Nagaraj.A
- Original Message -
From: "Kola Oyedeji" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, April 01, 2002 6:08 AM
Subject: OT: database design question re:product price changes


> Hi
>
> I have a table which holds orders. The productid and the quantity of items
> purchased are held in this table amongst other things such as the order
> date, customer id etc. Orders are calculated by getting the product cost
and
> multiplying it by quantity. Its occured to me that if the price of items
> changes,because the total of an order is always calculated the order total
> for previous orders will change. IS there any better way to store orders
and
> price info or should I also store the current price of each product when
the
> order is made in the order table - wouldn't this lead to data being
> duplicated?
>
> Thanks in avance
>
> Kola
>
> 
__
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: database design question re:product price changes

[nagraj]

Hi Kola Oyedeji,


  You Can Store the Product Price in orders table as it will save you from
making joins which is more overhead  then
querying same table and having the prices in it.


With Regards
Nagaraj.A

- Original Message -
From: "Kola Oyedeji" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, April 01, 2002 6:08 AM
Subject: OT: database design question re:product price changes


> Hi
>
> I have a table which holds orders. The productid and the quantity of items
> purchased are held in this table amongst other things such as the order
> date, customer id etc. Orders are calculated by getting the product cost
and
> multiplying it by quantity. Its occured to me that if the price of items
> changes,because the total of an order is always calculated the order total
> for previous orders will change. IS there any better way to store orders
and
> price info or should I also store the current price of each product when
the
> order is made in the order table - wouldn't this lead to data being
> duplicated?
>
> Thanks in avance
>
> Kola
>
> 
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: OT: database design question re:product price changes

[Tony Schreiber]

Agreed. Store the price the product was sold at in your orderitem table.

> You conceptually have 2 different prices
>
>   1 The Current Price usually stored in the Product File
>
>   2 The Order price (The Current Price when this order was filled)
> usually
>   stored in an order item file along with Qty (extended Item
> Amount Optional)
>
>
> 1. is a status field and can change at any time
>
> 2. Is a history field and "Never" changes *
>
> + Order adjustment transactions (separate transactions) are used to
> correct errors in 2 (and preserve an audit
> trail)
>
> If you store completed orders in the DB the order items should carry
> price 2
>
> HTH
>
> Dick
>
>
>
> On Sunday, March 31, 2002, at 04:38  PM, Kola Oyedeji wrote:
>
> > Hi
> >
> > I have a table which holds orders. The productid and the quantity of
> > items
> > purchased are held in this table amongst other things such as the order
> > date, customer id etc. Orders are calculated by getting the product
> > cost and
> > multiplying it by quantity. Its occured to me that if the price of items
> > changes,because the total of an order is always calculated the order
> > total
> > for previous orders will change. IS there any better way to store
> > orders and
> > price info or should I also store the current price of each product
> > when the
> > order is made in the order table - wouldn't this lead to data being
> > duplicated?
> >
> > Thanks in avance
> >
> > Kola
> >
> >
> 
__
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: OT: database design question re:product price changes

[Dick Applebaum]

You conceptually have 2 different prices

1 The Current Price usually stored in the Product File

2 The Order price (The Current Price when this order was filled) 
usually
  stored in an order item file along with Qty (extended Item 
Amount Optional)


1. is a status field and can change at any time

2. Is a history field and "Never" changes *

+ Order adjustment transactions (separate transactions) are used to 
correct errors in 2 (and preserve an audit
trail)

If you store completed orders in the DB the order items should carry 
price 2

HTH

Dick



On Sunday, March 31, 2002, at 04:38  PM, Kola Oyedeji wrote:

> Hi
>
> I have a table which holds orders. The productid and the quantity of 
> items
> purchased are held in this table amongst other things such as the order
> date, customer id etc. Orders are calculated by getting the product 
> cost and
> multiplying it by quantity. Its occured to me that if the price of items
> changes,because the total of an order is always calculated the order 
> total
> for previous orders will change. IS there any better way to store 
> orders and
> price info or should I also store the current price of each product 
> when the
> order is made in the order table - wouldn't this lead to data being
> duplicated?
>
> Thanks in avance
>
> Kola
>
> 
__
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

OT: database design question re:product price changes

[Kola Oyedeji]

Hi

I have a table which holds orders. The productid and the quantity of items
purchased are held in this table amongst other things such as the order
date, customer id etc. Orders are calculated by getting the product cost and
multiplying it by quantity. Its occured to me that if the price of items
changes,because the total of an order is always calculated the order total
for previous orders will change. IS there any better way to store orders and
price info or should I also store the current price of each product when the
order is made in the order table - wouldn't this lead to data being
duplicated?

Thanks in avance

Kola

__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: (OT) Database Design Question

[Jeffry Houser]

  It violates some principles of good database design.

  In the category table
  You cannot create a category without a user.  This is known as an 
insertion anomaly.
  If more than one user is located in more than one category, then you have
 
data replication, which could allow for data entry problems and is not 
space efficient.  Would you rather store the word 'Category" 100 times or 1
 
time and use it 100 times?


At 02:25 PM 01/30/2002 -0500, you wrote:
>-- Original Message --
>from: "Douglas Brown" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>date: Wed, 30 Jan 2002 11:43:41 -0800
>
>That looks like it would work fine. I prefer to use two tables instead
>of 3 myself. less joining.
>
>[user]
>id[PK]
>name
>address
>
>[category]
>id PK
>user_id (FK_USER.ID)
>cat_name
>
>
>
>
>There are two major products that come out of Berkeley: LSD and [Unix]
>BSD. We don't believe this to be a coincidence.
>
>
>
>Doug Brown
>- Original Message -
>From: "Jeffry Houser" <[EMAIL PROTECTED]>
>To: "CF-Talk" <[EMAIL PROTECTED]>
>Sent: Wednesday, January 30, 2002 10:34 AM
>Subject: Re: (OT) Database Design Question
>
>
> >   This is how I would handle it.  It sounds like a standard
>many-to-many
> > relationship.
> >
> > At 10:17 AM 01/30/2002 -0800, you wrote:
> > >I'm putting together a new database for a fairly substantial
>application I
> > >need to build.
> > >
> > >Here is the kind of information I need to store:
> > >
> > >-- Registration of individual (standard name, address, email type of
>stuff)
> > >-- Categories of interest
> > >
> > >The categories of interest will come from a predefined table of about
>100
> > >choices.  The user will be able to make multiple selections.
> > >
> > >Here is my tentative plan on how to store this information
> > >
> > >UserTable (UserID, name, address, etc.)
> > >CategoryTable (categoryID, categoryName)
> > >CategoryMatchTable (CategoryID, UserID)
> > >
> > >This last table would store one row each for each categoryID selected
>by
> >  the
> > >User matching it with each UserID.
> > >
> > >I've used this method on similar problems before, but I thought I
>would
> >  post
> > >the question here and see if any of you great minds know of a more
>elegant
> > >way to do this (match users with multiple selections from a list
>generated
> > >out of another table).
> > >
> > >H.
> > >
> >
>
>
__
Get Your Own Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: (OT) Database Design Question

[Dave Watts]

> That looks like it would work fine. I prefer to use two 
> tables instead of 3 myself. less joining.
> 
> [user]
> id[PK]
> name
> address
> 
> [category]
> id PK
> user_id (FK_USER.ID)
> cat_name

In your model, a category could only belong to one user.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
__
Get Your Own Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: (OT) Database Design Question

[Clint Tredway]

But with 2 tables, the user could not have an infinite amount of categoires
 *unless* the category table is a vertical table. You would have less joini
ng, but I do not think that its as flexible.

My 2cents..
Clint

-- Original Message --
from: "Douglas Brown" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
date: Wed, 30 Jan 2002 11:43:41 -0800

That looks like it would work fine. I prefer to use two tables instead 
of 3 myself. less joining.

[user]
id[PK]
name
address

[category]
id PK
user_id (FK_USER.ID)
cat_name




There are two major products that come out of Berkeley: LSD and [Unix] 
BSD. We don't believe this to be a coincidence.



Doug Brown
- Original Message - 
From: "Jeffry Houser" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 30, 2002 10:34 AM
Subject: Re: (OT) Database Design Question


>   This is how I would handle it.  It sounds like a standard 
many-to-many 
> relationship.
> 
> At 10:17 AM 01/30/2002 -0800, you wrote:
> >I'm putting together a new database for a fairly substantial 
application I
> >need to build.
> >
> >Here is the kind of information I need to store:
> >
> >-- Registration of individual (standard name, address, email type of 
stuff)
> >-- Categories of interest
> >
> >The categories of interest will come from a predefined table of about 
100
> >choices.  The user will be able to make multiple selections.
> >
> >Here is my tentative plan on how to store this information
> >
> >UserTable (UserID, name, address, etc.)
> >CategoryTable (categoryID, categoryName)
> >CategoryMatchTable (CategoryID, UserID)
> >
> >This last table would store one row each for each categoryID selected 
by
>  the
> >User matching it with each UserID.
> >
> >I've used this method on similar problems before, but I thought I 
would
>  post
> >the question here and see if any of you great minds know of a more 
elegant
> >way to do this (match users with multiple selections from a list 
generated
> >out of another table).
> >
> >H.
> >
> 

__
Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: (OT) Database Design Question

[Douglas Brown]

That looks like it would work fine. I prefer to use two tables instead 
of 3 myself. less joining.

[user]
id[PK]
name
address

[category]
id PK
user_id (FK_USER.ID)
cat_name




There are two major products that come out of Berkeley: LSD and [Unix] 
BSD. We don't believe this to be a coincidence.



Doug Brown
- Original Message - 
From: "Jeffry Houser" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 30, 2002 10:34 AM
Subject: Re: (OT) Database Design Question


>   This is how I would handle it.  It sounds like a standard 
many-to-many 
> relationship.
> 
> At 10:17 AM 01/30/2002 -0800, you wrote:
> >I'm putting together a new database for a fairly substantial 
application I
> >need to build.
> >
> >Here is the kind of information I need to store:
> >
> >-- Registration of individual (standard name, address, email type of 
stuff)
> >-- Categories of interest
> >
> >The categories of interest will come from a predefined table of about 
100
> >choices.  The user will be able to make multiple selections.
> >
> >Here is my tentative plan on how to store this information
> >
> >UserTable (UserID, name, address, etc.)
> >CategoryTable (categoryID, categoryName)
> >CategoryMatchTable (CategoryID, UserID)
> >
> >This last table would store one row each for each categoryID selected 
by
>  the
> >User matching it with each UserID.
> >
> >I've used this method on similar problems before, but I thought I 
would
>  post
> >the question here and see if any of you great minds know of a more 
elegant
> >way to do this (match users with multiple selections from a list 
generated
> >out of another table).
> >
> >H.
> >
> 
__
Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: (OT) Database Design Question

[Jeffry Houser]

  This is how I would handle it.  It sounds like a standard many-to-many 
relationship.

At 10:17 AM 01/30/2002 -0800, you wrote:
>I'm putting together a new database for a fairly substantial application I
>need to build.
>
>Here is the kind of information I need to store:
>
>-- Registration of individual (standard name, address, email type of stuff)
>-- Categories of interest
>
>The categories of interest will come from a predefined table of about 100
>choices.  The user will be able to make multiple selections.
>
>Here is my tentative plan on how to store this information
>
>UserTable (UserID, name, address, etc.)
>CategoryTable (categoryID, categoryName)
>CategoryMatchTable (CategoryID, UserID)
>
>This last table would store one row each for each categoryID selected by
 the
>User matching it with each UserID.
>
>I've used this method on similar problems before, but I thought I would
 post
>the question here and see if any of you great minds know of a more elegant
>way to do this (match users with multiple selections from a list generated
>out of another table).
>
>H.
>
__
Why Share?
  Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

(OT) Database Design Question

[Owens, Howard]

I'm putting together a new database for a fairly substantial application I
need to build.

Here is the kind of information I need to store:

-- Registration of individual (standard name, address, email type of stuff)
-- Categories of interest

The categories of interest will come from a predefined table of about 100
choices.  The user will be able to make multiple selections.

Here is my tentative plan on how to store this information

UserTable (UserID, name, address, etc.)
CategoryTable (categoryID, categoryName)
CategoryMatchTable (CategoryID, UserID) 

This last table would store one row each for each categoryID selected by the
User matching it with each UserID.

I've used this method on similar problems before, but I thought I would post
the question here and see if any of you great minds know of a more elegant
way to do this (match users with multiple selections from a list generated
out of another table).

H.
__
Dedicated Windows 2000 Server
  PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
  Instant Activation · $99/Month · Free Setup
  http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Kwang Suh]

If you have 3 tables, then there is no performance impact.  I'd keep it
normalized.

-Original Message-
From: Koo Pai Lao [mailto:[EMAIL PROTECTED]]
Sent: October 5, 2001 4:54 PM
To: CF-Talk
Subject: Database Design Question


ok, this is just a small example of what I am working with.

I have 3 tables, and their relationship is hierarchical.

TableA - id, name, year

TableB - id, description, TableA_id

TableC - id, time, location, TableB_id


ok, here is my question.  First off, i can relate information from TableA
with TableC through TableB, using outer joins or what not.  My question is,
would it save CPU time if I set up TableC like this...

TableC - id, time, location, TableB_id, TableA_id

so that I dont have to go through TableB to relate information between
TableA and TableC in my SQL statements??  Also, right now my head is telling
me that that might be a bad idea, since now if I edit the TableA_id in
TableB, i have the burden of editing it in TableC too.  But what are your
thoughts?

I'm just worried that too many wierd SQL statements will take more
processing time.
Ex..
Select this from there where b.id  is c.id AND c = d AND c.id  = b.id  and
c.id =  (u get the idea!!)
VS.
Select a from x where y = z (very simple!)



~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Database Design Question

[Koo Pai Lao]

ok, this is just a small example of what I am working with.

I have 3 tables, and their relationship is hierarchical.

TableA - id, name, year

TableB - id, description, TableA_id

TableC - id, time, location, TableB_id


ok, here is my question.  First off, i can relate information from TableA 
with TableC through TableB, using outer joins or what not.  My question is, 
would it save CPU time if I set up TableC like this...

TableC - id, time, location, TableB_id, TableA_id

so that I dont have to go through TableB to relate information between 
TableA and TableC in my SQL statements??  Also, right now my head is telling 
me that that might be a bad idea, since now if I edit the TableA_id in 
TableB, i have the burden of editing it in TableC too.  But what are your 
thoughts?

I'm just worried that too many wierd SQL statements will take more 
processing time.
Ex..
Select this from there where b.id  is c.id AND c = d AND c.id  = b.id  and 
c.id =  (u get the idea!!)
VS.
Select a from x where y = z (very simple!)


~~
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Pete Freitag]

The technique I was alluding to is called the Entity-Relationship (ER)
model. Do a search at your favorite book site, and you will find a bunch of
books on it.
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: Database Design Question

[G]

Generally speaking, this is not a good database design.  It's tempting
because of the myriad of string functions in Cold Fusion that make
manipulating strings like this fairly easy.  But from a pure database
standpoint, storing multiple information like this in one field is frowned
upon.

I think Pete's suggestions are worth looking intogood design up front
facilitates the inevitable changes and enhancements down the road.

Brian
- Original Message -
From: "Pooh Bear" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, May 21, 2001 1:12 PM
Subject: RE: Database Design Question


> Oh, you see, this project does not display the choices for the question,
nor
> does it displays the question.  And there's only 1 answer for each
question.
>   so I think 2 tables is the best way to handle it.  but I might consider
> putting the answers in one row for each exam like ABBCCCBBDDEEBDD, then
just
> use some string functions on them!
>
>
> >From: "Pete Freitag" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: RE: Database Design Question
> >Date: Mon, 21 May 2001 13:10:13 -0400
> >
> >Your DB can "probably" handle that many rows as long as its properly
> >indexed, but you should look at some database normalization techniques.
A
> >fully normalized design might have a table for...
> >
> >exams
> >questions
> >choices
> >
> >the correct answer should be a foreign key in questions table to the
> >choices
> >table.
> >
> >A good way to approach database design is to extract all nouns and make
> >them
> >entities.  Then determine the relationships between the entities (one to
> >many, many to many, or one to one).
> >
> >
> >Pete Freitag ([EMAIL PROTECTED])
> >CFDEV.COM
> >ColdFusion Developers Resources
> >http://www.cfdev.com/
> >
> >
> >-Original Message-
> >From: Pooh Bear [mailto:[EMAIL PROTECTED]]
> >Sent: Monday, May 21, 2001 12:15 PM
> >To: CF-Talk
> >Subject: Database Design Question
> >
> >
> >hey yall, I'm workin on this project where you can get answers to a
certain
> >exam (like if you're a teacher or instructor, u can use it to show to
your
> >students).  All of the answers are multiple choices.  Each exam has
> >anywhere
> >between 25 and 70 questions.  I want to know the best way to store the
> >answers.  Currently I am approaching it with 2 tables.  The first table
has
> >info about the exam (name, date, subject, etc)  the second table contains
> >the answers, and has the ExamID next to it (one row equals one answer).
I
> >was thinking that by doing that, I will get too many rows.  like what if
> >there are 500 exams.  25 times 500 is a lot of rows!  i am concerned
about
> >the speed.  Is there a way to store the answers?  or is the DB good
enough
> >to handle that much stuff?  i'm using SQL server 7.0, the box has 512mb
and
> >900mhz processor (it's a dev box also).  thank you! :)
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Pooh Bear]

Oh, you see, this project does not display the choices for the question, nor 
does it displays the question.  And there's only 1 answer for each question. 
  so I think 2 tables is the best way to handle it.  but I might consider 
putting the answers in one row for each exam like ABBCCCBBDDEEBDD, then just 
use some string functions on them!


>From: "Pete Freitag" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: RE: Database Design Question
>Date: Mon, 21 May 2001 13:10:13 -0400
>
>Your DB can "probably" handle that many rows as long as its properly
>indexed, but you should look at some database normalization techniques.  A
>fully normalized design might have a table for...
>
>exams
>questions
>choices
>
>the correct answer should be a foreign key in questions table to the 
>choices
>table.
>
>A good way to approach database design is to extract all nouns and make 
>them
>entities.  Then determine the relationships between the entities (one to
>many, many to many, or one to one).
>
>
>Pete Freitag ([EMAIL PROTECTED])
>CFDEV.COM
>ColdFusion Developers Resources
>http://www.cfdev.com/
>
>
>-Original Message-
>From: Pooh Bear [mailto:[EMAIL PROTECTED]]
>Sent: Monday, May 21, 2001 12:15 PM
>To: CF-Talk
>Subject: Database Design Question
>
>
>hey yall, I'm workin on this project where you can get answers to a certain
>exam (like if you're a teacher or instructor, u can use it to show to your
>students).  All of the answers are multiple choices.  Each exam has 
>anywhere
>between 25 and 70 questions.  I want to know the best way to store the
>answers.  Currently I am approaching it with 2 tables.  The first table has
>info about the exam (name, date, subject, etc)  the second table contains
>the answers, and has the ExamID next to it (one row equals one answer).  I
>was thinking that by doing that, I will get too many rows.  like what if
>there are 500 exams.  25 times 500 is a lot of rows!  i am concerned about
>the speed.  Is there a way to store the answers?  or is the DB good enough
>to handle that much stuff?  i'm using SQL server 7.0, the box has 512mb and
>900mhz processor (it's a dev box also).  thank you! :)
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Pete Freitag]

Your DB can "probably" handle that many rows as long as its properly
indexed, but you should look at some database normalization techniques.  A
fully normalized design might have a table for...

exams
questions
choices

the correct answer should be a foreign key in questions table to the choices
table.

A good way to approach database design is to extract all nouns and make them
entities.  Then determine the relationships between the entities (one to
many, many to many, or one to one).


Pete Freitag ([EMAIL PROTECTED])
CFDEV.COM
ColdFusion Developers Resources
http://www.cfdev.com/


-Original Message-
From: Pooh Bear [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 21, 2001 12:15 PM
To: CF-Talk
Subject: Database Design Question


hey yall, I'm workin on this project where you can get answers to a certain
exam (like if you're a teacher or instructor, u can use it to show to your
students).  All of the answers are multiple choices.  Each exam has anywhere
between 25 and 70 questions.  I want to know the best way to store the
answers.  Currently I am approaching it with 2 tables.  The first table has
info about the exam (name, date, subject, etc)  the second table contains
the answers, and has the ExamID next to it (one row equals one answer).  I
was thinking that by doing that, I will get too many rows.  like what if
there are 500 exams.  25 times 500 is a lot of rows!  i am concerned about
the speed.  Is there a way to store the answers?  or is the DB good enough
to handle that much stuff?  i'm using SQL server 7.0, the box has 512mb and
900mhz processor (it's a dev box also).  thank you! :)
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Bob Silverberg]

If you want to store all of the answers (not just the correct one) for each
question, I think you're actually looking at 3 tables:

tblExam - ExamId(PK), ExamName, ExamDate, etc.
tblQuestion - QuestionID(PK), ExamId(FK), QuestionText, etc.
tblAnswer - AnswerId(PK), QuestionID(FK), AnswerText, IsCorrectFlag, etc.

This will also allow you to support questions that have multiple answers.

I don't think you'll run into performance issues with the setup that you
described (at least not with this database - other things on the machine
might affect it).   SQL Server is designed to handle a lot more than a few
thousand rows.

HTH,
Bob

-Original Message-
From: Pooh Bear [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 21, 2001 12:15 PM
To: CF-Talk
Subject: Database Design Question


hey yall, I'm workin on this project where you can get answers to a certain
exam (like if you're a teacher or instructor, u can use it to show to your
students).  All of the answers are multiple choices.  Each exam has anywhere
between 25 and 70 questions.  I want to know the best way to store the
answers.  Currently I am approaching it with 2 tables.  The first table has
info about the exam (name, date, subject, etc)  the second table contains
the answers, and has the ExamID next to it (one row equals one answer).  I
was thinking that by doing that, I will get too many rows.  like what if
there are 500 exams.  25 times 500 is a lot of rows!  i am concerned about
the speed.  Is there a way to store the answers?  or is the DB good enough
to handle that much stuff?  i'm using SQL server 7.0, the box has 512mb and
900mhz processor (it's a dev box also).  thank you! :)
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Russ Conway]

First 12.5k rows is not very many. I've worked on SQL Server apps with
closer to 1 million rows. If you index the tables correctly and run
reasonable queries, I'd be surprised if you taxed the server too much.

As an alternate strategy though, you could do this with one table. Just
create a field on your exam table like this...

ANSWERS CHAR(25);

Then store the answers in it like this...

ABDCADACBDBDCADBCADBC

or this...

432412341142314234143


Russell Conway
HallofSports.com, Inc.
351 West 22nd Street
New York, NY 10011
P (646) 638-2500
F (561) 423-2674
http://www.hallofsports.com
.. . . where the legends live on

> -Original Message-
> From: Pooh Bear [mailto:[EMAIL PROTECTED]]
> Sent: Monday, May 21, 2001 12:15 PM
> To: CF-Talk
> Subject: Database Design Question
>
>
> hey yall, I'm workin on this project where you can get answers to
> a certain
> exam (like if you're a teacher or instructor, u can use it to
> show to your
> students).  All of the answers are multiple choices.  Each exam
> has anywhere
> between 25 and 70 questions.  I want to know the best way to store the
> answers.  Currently I am approaching it with 2 tables.  The first
> table has
> info about the exam (name, date, subject, etc)  the second table contains
> the answers, and has the ExamID next to it (one row equals one
> answer).  I
> was thinking that by doing that, I will get too many rows.  like what if
> there are 500 exams.  25 times 500 is a lot of rows!  i am
> concerned about
> the speed.  Is there a way to store the answers?  or is the DB
> good enough
> to handle that much stuff?  i'm using SQL server 7.0, the box has
> 512mb and
> 900mhz processor (it's a dev box also).  thank you! :)
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Database Design Question

[Pooh Bear]

hey yall, I'm workin on this project where you can get answers to a certain 
exam (like if you're a teacher or instructor, u can use it to show to your 
students).  All of the answers are multiple choices.  Each exam has anywhere 
between 25 and 70 questions.  I want to know the best way to store the 
answers.  Currently I am approaching it with 2 tables.  The first table has 
info about the exam (name, date, subject, etc)  the second table contains 
the answers, and has the ExamID next to it (one row equals one answer).  I 
was thinking that by doing that, I will get too many rows.  like what if 
there are 500 exams.  25 times 500 is a lot of rows!  i am concerned about 
the speed.  Is there a way to store the answers?  or is the DB good enough 
to handle that much stuff?  i'm using SQL server 7.0, the box has 512mb and 
900mhz processor (it's a dev box also).  thank you! :)
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: database design question

[Phoeun Pha]

I don't think the question is whether using too many tables will affect the
speed.  But which is the best way to design the database. and u said "up to
15 tables".  why 15?  the one i am working on uses over 70+ tables

-Original Message-
From: Julia Green [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 30, 2001 1:05 PM
To: CF-Talk
Subject: Re: database design question


Well, as a Web Database programmer at UPS who works with
Access tables a great deal, when used as an interface with ColdFusion,
you can have up to 15 tables all supporting your websites with no
down time.

Usually people want only functional tables, so they can export
them to the server a little faster.  But once there, as many tables
as you need and want and just as fast or slow with good ColdFusion
interfaces...


Julia Green
Julia Computer Consulting
www.juliagreen.com
Email:  [EMAIL PROTECTED]
Phone:  617-926-3413
PO Box 279
Watertown MA  02471-0279
Fax:  413-771-0306
- Original Message -
From: Julie Clegg <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 4:25 PM
Subject: database design question


>
> hello,
>
> I am putting together a database and I cannot decide what to do with the
> Education Table.  I have 1000 clients who I need to enter their education
> level.  For example, did they graduate from highschool and if so, what is
> the name of their highschool.  Should I put that info on the same table or
> create a new table with the clientid and the name of the highchool
attended
> if they did attend.  I know that if I put it all on one table there will
be
> a lot of empty fields but I also didnt know if it was "wasteful" to create
> such a small table just for highschool name.
>
> What do you think?  Will the system slow down if I have ALOT of tables?
>
> Thanks,
>
> Julie
>
> >From: "Julia Green" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: Re: CF Books
> >Date: Tue, 20 Mar 2001 20:00:26 -0500
> >
> >Julie --
> >
> >This is late in coming, bu I have a great Adobe Acrobat Documentation
book:
> >
> >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> >
> >Julia Green
> >Julia Computer Consulting
> >PO Box 279
> >Watertown MA 02471-0279
> >http://www.juliagreen.com
> >Email:  [EMAIL PROTECTED]
> >Phone:  617-926-3413
> >FAX:  413-771-0306
> >
> >- Original Message -
> >From: Julie Clegg <[EMAIL PROTECTED]>
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Sent: Monday, March 19, 2001 11:05 AM
> >Subject: CF Books
> >
> >
> > > Hello,
> > >
> > > Can anyone recommend a good CF developers book...we will be building a
> >new
> > > application using CF 4.5 and I need something that will be a good
> >reference
> > > book for a new CF developer!
> > >
> > > Thanks,
> > >
> > > Julie
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: [Re: database design question]

[Alex]

why only up to 15 tables?

"Julia Green" <[EMAIL PROTECTED]> wrote:
Well, as a Web Database programmer at UPS who works with
Access tables a great deal, when used as an interface with ColdFusion,
you can have up to 15 tables all supporting your websites with no
down time.

Usually people want only functional tables, so they can export
them to the server a little faster.  But once there, as many tables
as you need and want and just as fast or slow with good ColdFusion
interfaces...


Julia Green
Julia Computer Consulting
www.juliagreen.com
Email:  [EMAIL PROTECTED]
Phone:  617-926-3413
PO Box 279
Watertown MA  02471-0279
Fax:  413-771-0306
- Original Message -
From: Julie Clegg <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 4:25 PM
Subject: database design question


>
> hello,
>
> I am putting together a database and I cannot decide what to do with the
> Education Table.  I have 1000 clients who I need to enter their education
> level.  For example, did they graduate from highschool and if so, what is
> the name of their highschool.  Should I put that info on the same table or
> create a new table with the clientid and the name of the highchool
attended
> if they did attend.  I know that if I put it all on one table there will
be
> a lot of empty fields but I also didnt know if it was "wasteful" to create
> such a small table just for highschool name.
>
> What do you think?  Will the system slow down if I have ALOT of tables?
>
> Thanks,
>
> Julie
>
> >From: "Julia Green" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: Re: CF Books
> >Date: Tue, 20 Mar 2001 20:00:26 -0500
> >
> >Julie --
> >
> >This is late in coming, bu I have a great Adobe Acrobat Documentation
book:
> >
> >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> >
> >Julia Green
> >Julia Computer Consulting
> >PO Box 279
> >Watertown MA 02471-0279
> >http://www.juliagreen.com
> >Email:  [EMAIL PROTECTED]
> >Phone:  617-926-3413
> >FAX:  413-771-0306
> >
> >- Original Message -
> >From: Julie Clegg <[EMAIL PROTECTED]>
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Sent: Monday, March 19, 2001 11:05 AM
> >Subject: CF Books
> >
> >
> > > Hello,
> > >
> > > Can anyone recommend a good CF developers book...we will be building a
> >new
> > > application using CF 4.5 and I need something that will be a good
> >reference
> > > book for a new CF developer!
> > >
> > > Thanks,
> > >
> > > Julie
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: database design question

[Julia Green]

Well, as a Web Database programmer at UPS who works with
Access tables a great deal, when used as an interface with ColdFusion,
you can have up to 15 tables all supporting your websites with no
down time.

Usually people want only functional tables, so they can export
them to the server a little faster.  But once there, as many tables
as you need and want and just as fast or slow with good ColdFusion
interfaces...


Julia Green
Julia Computer Consulting
www.juliagreen.com
Email:  [EMAIL PROTECTED]
Phone:  617-926-3413
PO Box 279
Watertown MA  02471-0279
Fax:  413-771-0306
- Original Message -
From: Julie Clegg <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 4:25 PM
Subject: database design question


>
> hello,
>
> I am putting together a database and I cannot decide what to do with the
> Education Table.  I have 1000 clients who I need to enter their education
> level.  For example, did they graduate from highschool and if so, what is
> the name of their highschool.  Should I put that info on the same table or
> create a new table with the clientid and the name of the highchool
attended
> if they did attend.  I know that if I put it all on one table there will
be
> a lot of empty fields but I also didnt know if it was "wasteful" to create
> such a small table just for highschool name.
>
> What do you think?  Will the system slow down if I have ALOT of tables?
>
> Thanks,
>
> Julie
>
> >From: "Julia Green" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: Re: CF Books
> >Date: Tue, 20 Mar 2001 20:00:26 -0500
> >
> >Julie --
> >
> >This is late in coming, bu I have a great Adobe Acrobat Documentation
book:
> >
> >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> >
> >Julia Green
> >Julia Computer Consulting
> >PO Box 279
> >Watertown MA 02471-0279
> >http://www.juliagreen.com
> >Email:  [EMAIL PROTECTED]
> >Phone:  617-926-3413
> >FAX:  413-771-0306
> >
> >- Original Message -
> >From: Julie Clegg <[EMAIL PROTECTED]>
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Sent: Monday, March 19, 2001 11:05 AM
> >Subject: CF Books
> >
> >
> > > Hello,
> > >
> > > Can anyone recommend a good CF developers book...we will be building a
> >new
> > > application using CF 4.5 and I need something that will be a good
> >reference
> > > book for a new CF developer!
> > >
> > > Thanks,
> > >
> > > Julie
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: database design question

[Tony Schreiber]

Um, that's why I said:

"and there certainly could be depending on the application - ie. What
school did you graduate from? vs. What school(s) have you attended"

And that's my point. For ONE school, a separate relational table is
unnecessary.

> grr!! it does fit in right.  it said GRADUATED FROM.  i've never known of
> anyone who has graduated from more than one high school :P
>
> - Original Message -
> From: Tony Schreiber <[EMAIL PROTECTED]>
> To: CF-Talk <[EMAIL PROTECTED]>
> Sent: Wednesday, March 28, 2001 10:36 PM
> Subject: RE: database design question
> 
> 
> > I think your highschoolrelations table is overkill when there is only a
> > 1-1 relationship. If there were a small, finite number of schools, then
> > yes, a table of ids and names of those schools would be useful and you
> > would populate the highschool field of the user with that schools id. But
> > if there could be more than one school per user (and there certainly could
> > be depending on the application - ie. What school did you graduate from?
> > vs. What school(s) have you attended) then your relations table fits the
> > bill nicely.
> >
> > > Here is the question.  Diod they graduate from high school?  If so,
> which
> > > high school?
> > >
> > > Here is the inference.
> > >
> > > Those who have a high school name went to high school, and those who
> didn't
> > > have a high school name did not go to high school.  i know it's  a big
> DUH,
> > > but it's important.
> > >
> > > here's how i would dersign the database.  I would treat the high school
> name
> > > as a city, state or zip code, because there are thousands or gagillions
> of
> > > em.  So, i would put them in the same table as the client name
> > >
> > > so the client table would have another field called high school.  if
> it's
> > > null, then that means that they did not graduate from high school, and u
> can
> > > use that as a condition when u need that information.
> > >
> > > IF there were like only 10 high schools in the world, I would go about a
> > > different approach.
> > >
> > > I would have 3 tables.   1 table has the name of all the clients and
> their
> > > clientID (increment).  the 2nd table has the list of all high schools
> and
> > > their highschoolID (increment). The third table builds the relationship
> > > between the clients and their school.  the third table would have 3
> fields.
> > > The HighSchoolrelationsID, ClientID, and highschoolID.
> > >
> > > So if client Amy Gershen had a clientID of 3, and her high school James
> > > Brown High had a highschoolID of 12, this is the informationt hat would
> go
> > > into the Highschoolrelations table.
> > >
> > > HighschoolrelationsID ClientID HighSchoolID
> > > -  
> > >  1   3  12
> > >
> > >
> > >
> > > -Original Message-
> > > From: Julie Clegg [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, March 28, 2001 3:25 PM
> > > To: CF-Talk
> > > Subject: database design question
> > >
> > >
> > >
> > > hello,
> > >
> > > I am putting together a database and I cannot decide what to do with the
> > > Education Table.  I have 1000 clients who I need to enter their
> education
> > > level.  For example, did they graduate from highschool and if so, what
> is
> > > the name of their highschool.  Should I put that info on the same table
> or
> > > create a new table with the clientid and the name of the highchool
> attended
> > > if they did attend.  I know that if I put it all on one table there will
> be
> > > a lot of empty fields but I also didnt know if it was "wasteful" to
> create
> > > such a small table just for highschool name.
> > >
> > > What do you think?  Will the system slow down if I have ALOT of tables?
> > >
> > > Thanks,
> > >
> > > Julie
> > >
> > > >From: "Julia Green" <[EMAIL PROTECTED]>
> > > >Reply-To: [EMAIL PROTECTED]
> > > >To: CF-Talk <[EMAIL PROTECTED]>
> > > >Subject: Re: CF Books
> > > >Date: Tue, 20 Mar 2001 20:00:26 -0500
> > > >
> > > >Julie --
> > > >
> > &g

Re: database design question

[Phoeun Pha]

grr!! it does fit in right.  it said GRADUATED FROM.  i've never known of
anyone who has graduated from more than one high school :P
- Original Message -
From: Tony Schreiber <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 10:36 PM
Subject: RE: database design question


> I think your highschoolrelations table is overkill when there is only a
> 1-1 relationship. If there were a small, finite number of schools, then
> yes, a table of ids and names of those schools would be useful and you
> would populate the highschool field of the user with that schools id. But
> if there could be more than one school per user (and there certainly could
> be depending on the application - ie. What school did you graduate from?
> vs. What school(s) have you attended) then your relations table fits the
> bill nicely.
>
> > Here is the question.  Diod they graduate from high school?  If so,
which
> > high school?
> >
> > Here is the inference.
> >
> > Those who have a high school name went to high school, and those who
didn't
> > have a high school name did not go to high school.  i know it's  a big
DUH,
> > but it's important.
> >
> > here's how i would dersign the database.  I would treat the high school
name
> > as a city, state or zip code, because there are thousands or gagillions
of
> > em.  So, i would put them in the same table as the client name
> >
> > so the client table would have another field called high school.  if
it's
> > null, then that means that they did not graduate from high school, and u
can
> > use that as a condition when u need that information.
> >
> > IF there were like only 10 high schools in the world, I would go about a
> > different approach.
> >
> > I would have 3 tables.   1 table has the name of all the clients and
their
> > clientID (increment).  the 2nd table has the list of all high schools
and
> > their highschoolID (increment). The third table builds the relationship
> > between the clients and their school.  the third table would have 3
fields.
> > The HighSchoolrelationsID, ClientID, and highschoolID.
> >
> > So if client Amy Gershen had a clientID of 3, and her high school James
> > Brown High had a highschoolID of 12, this is the informationt hat would
go
> > into the Highschoolrelations table.
> >
> > HighschoolrelationsID ClientID HighSchoolID
> > -     ---- 
> >  1   3  12
> >
> >
> >
> > -Original Message-
> > From: Julie Clegg [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, March 28, 2001 3:25 PM
> > To: CF-Talk
> > Subject: database design question
> >
> >
> >
> > hello,
> >
> > I am putting together a database and I cannot decide what to do with the
> > Education Table.  I have 1000 clients who I need to enter their
education
> > level.  For example, did they graduate from highschool and if so, what
is
> > the name of their highschool.  Should I put that info on the same table
or
> > create a new table with the clientid and the name of the highchool
attended
> > if they did attend.  I know that if I put it all on one table there will
be
> > a lot of empty fields but I also didnt know if it was "wasteful" to
create
> > such a small table just for highschool name.
> >
> > What do you think?  Will the system slow down if I have ALOT of tables?
> >
> > Thanks,
> >
> > Julie
> >
> > >From: "Julia Green" <[EMAIL PROTECTED]>
> > >Reply-To: [EMAIL PROTECTED]
> > >To: CF-Talk <[EMAIL PROTECTED]>
> > >Subject: Re: CF Books
> > >Date: Tue, 20 Mar 2001 20:00:26 -0500
> > >
> > >Julie --
> > >
> > >This is late in coming, bu I have a great Adobe Acrobat Documentation
book:
> > >
> > >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> > >
> > >Julia Green
> > >Julia Computer Consulting
> > >PO Box 279
> > >Watertown MA 02471-0279
> > >http://www.juliagreen.com
> > >Email:  [EMAIL PROTECTED]
> > >Phone:  617-926-3413
> > >FAX:  413-771-0306
> > >
> > >- Original Message -
> > >From: Julie Clegg <[EMAIL PROTECTED]>
> > >To: CF-Talk <[EMAIL PROTECTED]>
> > >Sent: Monday, March 19, 2001 11:05 AM
> > >Subject: CF Books
> > >
> > >
> > > > Hello,
> > > >
> > > > Can anyone recommend a good CF developers book...we will be building
a
> > >new
> > > > application using CF 4.5 and I need something that will be a good
> > >reference
> > > > book for a new CF developer!
> > > >
> > > > Thanks,
> > > >
> > > > Julie
> > > >
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: database design question

[Tony Schreiber]

I think your highschoolrelations table is overkill when there is only a
1-1 relationship. If there were a small, finite number of schools, then
yes, a table of ids and names of those schools would be useful and you
would populate the highschool field of the user with that schools id. But
if there could be more than one school per user (and there certainly could
be depending on the application - ie. What school did you graduate from?
vs. What school(s) have you attended) then your relations table fits the
bill nicely.

> Here is the question.  Diod they graduate from high school?  If so, which
> high school?
> 
> Here is the inference.
> 
> Those who have a high school name went to high school, and those who didn't
> have a high school name did not go to high school.  i know it's  a big DUH,
> but it's important.
> 
> here's how i would dersign the database.  I would treat the high school name
> as a city, state or zip code, because there are thousands or gagillions of
> em.  So, i would put them in the same table as the client name
> 
> so the client table would have another field called high school.  if it's
> null, then that means that they did not graduate from high school, and u can
> use that as a condition when u need that information.
> 
> IF there were like only 10 high schools in the world, I would go about a
> different approach.
> 
> I would have 3 tables.   1 table has the name of all the clients and their
> clientID (increment).  the 2nd table has the list of all high schools and
> their highschoolID (increment). The third table builds the relationship
> between the clients and their school.  the third table would have 3 fields.
> The HighSchoolrelationsID, ClientID, and highschoolID.
> 
> So if client Amy Gershen had a clientID of 3, and her high school James
> Brown High had a highschoolID of 12, this is the informationt hat would go
> into the Highschoolrelations table.
> 
> HighschoolrelationsID ClientID HighSchoolID
> -  
>  1   3  12
> 
> 
> 
> -Original Message-
> From: Julie Clegg [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 28, 2001 3:25 PM
> To: CF-Talk
> Subject: database design question
> 
> 
> 
> hello,
> 
> I am putting together a database and I cannot decide what to do with the 
> Education Table.  I have 1000 clients who I need to enter their education 
> level.  For example, did they graduate from highschool and if so, what is 
> the name of their highschool.  Should I put that info on the same table or 
> create a new table with the clientid and the name of the highchool attended 
> if they did attend.  I know that if I put it all on one table there will be 
> a lot of empty fields but I also didnt know if it was "wasteful" to create 
> such a small table just for highschool name.
> 
> What do you think?  Will the system slow down if I have ALOT of tables?
> 
> Thanks,
> 
> Julie
> 
> >From: "Julia Green" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: Re: CF Books
> >Date: Tue, 20 Mar 2001 20:00:26 -0500
> >
> >Julie --
> >
> >This is late in coming, bu I have a great Adobe Acrobat Documentation book:
> >
> >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> >
> >Julia Green
> >Julia Computer Consulting
> >PO Box 279
> >Watertown MA 02471-0279
> >http://www.juliagreen.com
> >Email:  [EMAIL PROTECTED]
> >Phone:  617-926-3413
> >FAX:  413-771-0306
> >
> >- Original Message -
> >From: Julie Clegg <[EMAIL PROTECTED]>
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Sent: Monday, March 19, 2001 11:05 AM
> >Subject: CF Books
> >
> >
> > > Hello,
> > >
> > > Can anyone recommend a good CF developers book...we will be building a 
> >new
> > > application using CF 4.5 and I need something that will be a good
> >reference
> > > book for a new CF developer!
> > >
> > > Thanks,
> > >
> > > Julie
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: database design question

[Tony Schreiber]

While Juile obviously understands the idea of relational databases, I
think the question is more along the lines of balancing the number of
fields in a table with the number of tables you have. This is related to
the recent thread regarding, dang what was it?, well anyway, it's the same
thing.

If it's just ONE field that may or may not be null, I'd say put it in the
same table as your user data.

If that Education information was more than just ONE field (School,
Degree, Specialty, Location ,etc), then you'd probably want to move all
the Education information to a separate table.

> time for a book on relational databases
> 
> 
>   Terry Bader
> 
> > -Original Message-
> > From: Julie Clegg [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, March 28, 2001 4:25 PM
> > To: CF-Talk
> > Subject: database design question
> > 
> > 
> > 
> > hello,
> > 
> > I am putting together a database and I cannot decide what to 
> > do with the 
> > Education Table.  I have 1000 clients who I need to enter 
> > their education 
> > level.  For example, did they graduate from highschool and if 
> > so, what is 
> > the name of their highschool.  Should I put that info on the 
> > same table or 
> > create a new table with the clientid and the name of the 
> > highchool attended 
> > if they did attend.  I know that if I put it all on one table 
> > there will be 
> > a lot of empty fields but I also didnt know if it was 
> > "wasteful" to create 
> > such a small table just for highschool name.
> > 
> > What do you think?  Will the system slow down if I have ALOT 
> > of tables?
> > 
> > Thanks,
> > 
> > Julie
> > 
> > >From: "Julia Green" <[EMAIL PROTECTED]>
> > >Reply-To: [EMAIL PROTECTED]
> > >To: CF-Talk <[EMAIL PROTECTED]>
> > >Subject: Re: CF Books
> > >Date: Tue, 20 Mar 2001 20:00:26 -0500
> > >
> > >Julie --
> > >
> > >This is late in coming, bu I have a great Adobe Acrobat 
> > Documentation book:
> > >
> > >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> > >
> > >Julia Green
> > >Julia Computer Consulting
> > >PO Box 279
> > >Watertown MA 02471-0279
> > >http://www.juliagreen.com
> > >Email:  [EMAIL PROTECTED]
> > >Phone:  617-926-3413
> > >FAX:  413-771-0306
> > >
> > >- Original Message -
> > >From: Julie Clegg <[EMAIL PROTECTED]>
> > >To: CF-Talk <[EMAIL PROTECTED]>
> > >Sent: Monday, March 19, 2001 11:05 AM
> > >Subject: CF Books
> > >
> > >
> > > > Hello,
> > > >
> > > > Can anyone recommend a good CF developers book...we will 
> > be building a 
> > >new
> > > > application using CF 4.5 and I need something that will be a good
> > >reference
> > > > book for a new CF developer!
> > > >
> > > > Thanks,
> > > >
> > > > Julie
> > > >
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: database design question

[Judith Taylor]

An excellent on designing relational databases is Database Design for Mere 
Mortals by Michael J. Hernandez. It has definitely helped me.

Judith

Julie Clegg put into words:

>hello,
>
>I am putting together a database and I cannot decide what to do with the
>Education Table.  I have 1000 clients who I need to enter their education
>level.  For example, did they graduate from highschool and if so, what is
>the name of their highschool.  Should I put that info on the same table or
>create a new table with the clientid and the name of the highchool attended
>if they did attend.  I know that if I put it all on one table there will be
>a lot of empty fields but I also didnt know if it was "wasteful" to create
>such a small table just for highschool name.
>
>What do you think?  Will the system slow down if I have ALOT of tables?
>
>Thanks,
>
>Julie

Judith Taylor
ICQ: 67460562
Freelance ColdFusion Developer

Freinds don't let friends code before coffee.


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: database design question

[Phoeun Pha]

Here is the question.  Diod they graduate from high school?  If so, which
high school?

Here is the inference.

Those who have a high school name went to high school, and those who didn't
have a high school name did not go to high school.  i know it's  a big DUH,
but it's important.

here's how i would dersign the database.  I would treat the high school name
as a city, state or zip code, because there are thousands or gagillions of
em.  So, i would put them in the same table as the client name

so the client table would have another field called high school.  if it's
null, then that means that they did not graduate from high school, and u can
use that as a condition when u need that information.

IF there were like only 10 high schools in the world, I would go about a
different approach.

I would have 3 tables.   1 table has the name of all the clients and their
clientID (increment).  the 2nd table has the list of all high schools and
their highschoolID (increment). The third table builds the relationship
between the clients and their school.  the third table would have 3 fields.
The HighSchoolrelationsID, ClientID, and highschoolID.

So if client Amy Gershen had a clientID of 3, and her high school James
Brown High had a highschoolID of 12, this is the informationt hat would go
into the Highschoolrelations table.

HighschoolrelationsID ClientID HighSchoolID
-  
 1   3  12








-Original Message-
From: Julie Clegg [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 3:25 PM
To: CF-Talk
Subject: database design question



hello,

I am putting together a database and I cannot decide what to do with the 
Education Table.  I have 1000 clients who I need to enter their education 
level.  For example, did they graduate from highschool and if so, what is 
the name of their highschool.  Should I put that info on the same table or 
create a new table with the clientid and the name of the highchool attended 
if they did attend.  I know that if I put it all on one table there will be 
a lot of empty fields but I also didnt know if it was "wasteful" to create 
such a small table just for highschool name.

What do you think?  Will the system slow down if I have ALOT of tables?

Thanks,

Julie

>From: "Julia Green" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: Re: CF Books
>Date: Tue, 20 Mar 2001 20:00:26 -0500
>
>Julie --
>
>This is late in coming, bu I have a great Adobe Acrobat Documentation book:
>
>http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
>
>Julia Green
>Julia Computer Consulting
>PO Box 279
>Watertown MA 02471-0279
>http://www.juliagreen.com
>Email:  [EMAIL PROTECTED]
>Phone:  617-926-3413
>FAX:  413-771-0306
>
>- Original Message -
>From: Julie Clegg <[EMAIL PROTECTED]>
>To: CF-Talk <[EMAIL PROTECTED]>
>Sent: Monday, March 19, 2001 11:05 AM
>Subject: CF Books
>
>
> > Hello,
> >
> > Can anyone recommend a good CF developers book...we will be building a 
>new
> > application using CF 4.5 and I need something that will be a good
>reference
> > book for a new CF developer!
> >
> > Thanks,
> >
> > Julie
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: database design question

[Terry Bader]

time for a book on relational databases


Terry Bader
IT/Web Specialist
EDO Corp - Combat Systems
(757) 424-1004 ext 361 - Work
[EMAIL PROTECTED]   


(757)581-5981 - Mobile
[EMAIL PROTECTED]
icq: 5202487   aim: lv2bounce
http://www.cs.odu.edu/~bader






> -Original Message-
> From: Julie Clegg [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 28, 2001 4:25 PM
> To: CF-Talk
> Subject: database design question
> 
> 
> 
> hello,
> 
> I am putting together a database and I cannot decide what to 
> do with the 
> Education Table.  I have 1000 clients who I need to enter 
> their education 
> level.  For example, did they graduate from highschool and if 
> so, what is 
> the name of their highschool.  Should I put that info on the 
> same table or 
> create a new table with the clientid and the name of the 
> highchool attended 
> if they did attend.  I know that if I put it all on one table 
> there will be 
> a lot of empty fields but I also didnt know if it was 
> "wasteful" to create 
> such a small table just for highschool name.
> 
> What do you think?  Will the system slow down if I have ALOT 
> of tables?
> 
> Thanks,
> 
> Julie
> 
> >From: "Julia Green" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: Re: CF Books
> >Date: Tue, 20 Mar 2001 20:00:26 -0500
> >
> >Julie --
> >
> >This is late in coming, bu I have a great Adobe Acrobat 
> Documentation book:
> >
> >http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
> >
> >Julia Green
> >Julia Computer Consulting
> >PO Box 279
> >Watertown MA 02471-0279
> >http://www.juliagreen.com
> >Email:  [EMAIL PROTECTED]
> >Phone:  617-926-3413
> >FAX:  413-771-0306
> >
> >- Original Message -
> >From: Julie Clegg <[EMAIL PROTECTED]>
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Sent: Monday, March 19, 2001 11:05 AM
> >Subject: CF Books
> >
> >
> > > Hello,
> > >
> > > Can anyone recommend a good CF developers book...we will 
> be building a 
> >new
> > > application using CF 4.5 and I need something that will be a good
> >reference
> > > book for a new CF developer!
> > >
> > > Thanks,
> > >
> > > Julie
> > >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

database design question

[Julie Clegg]

hello,

I am putting together a database and I cannot decide what to do with the 
Education Table.  I have 1000 clients who I need to enter their education 
level.  For example, did they graduate from highschool and if so, what is 
the name of their highschool.  Should I put that info on the same table or 
create a new table with the clientid and the name of the highchool attended 
if they did attend.  I know that if I put it all on one table there will be 
a lot of empty fields but I also didnt know if it was "wasteful" to create 
such a small table just for highschool name.

What do you think?  Will the system slow down if I have ALOT of tables?

Thanks,

Julie

>From: "Julia Green" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: Re: CF Books
>Date: Tue, 20 Mar 2001 20:00:26 -0500
>
>Julie --
>
>This is late in coming, bu I have a great Adobe Acrobat Documentation book:
>
>http://www.allaire.com/documents/cf45docs/acrobatdocs/45dwa.pdf
>
>Julia Green
>Julia Computer Consulting
>PO Box 279
>Watertown MA 02471-0279
>http://www.juliagreen.com
>Email:  [EMAIL PROTECTED]
>Phone:  617-926-3413
>FAX:  413-771-0306
>
>- Original Message -
>From: Julie Clegg <[EMAIL PROTECTED]>
>To: CF-Talk <[EMAIL PROTECTED]>
>Sent: Monday, March 19, 2001 11:05 AM
>Subject: CF Books
>
>
> > Hello,
> >
> > Can anyone recommend a good CF developers book...we will be building a 
>new
> > application using CF 4.5 and I need something that will be a good
>reference
> > book for a new CF developer!
> >
> > Thanks,
> >
> > Julie
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: Database design question

[Todd Ashworth]

Well .. the requirements are as follows.  Teachers may be scheduled without
knowing when classes are scheduled, or classes may be scheduled without
knowing when teachers are scheduled, or .. both.  The administrator might
base the class schedule upon when teacher(s) can work, or they may base the
teacher schedule upon when classes need to be scheduled .. in which case,
may depend on when the students can attend.  So, in effect, the admin needs
to be able to schedule a teacher without a class and a class without a
teacher and both depending on when the students can attend ... maybe.
That's the main prob.  We are running into too many *maybes* and are trying
to code for/design for them all.  As a developer, I would be more than
willing to make compromises, but in this case, all situations need to be
designed/coded for, since there are so many possibilities.

My other comments are in line below:

- Original Message -
From: "Jeffry Houser" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, November 16, 2000 9:36 PM
Subject: Re: Database design question


>
>  I consider this type of topic my forte, so I hope I can help.
> Although, not a CF-specific question, I think this definitely
> is something anyone should know before even starting to code in
> ColdFusion.
>
> > We are writing an application part of which handles scheduling of
"classes"
> > and "teachers" work schedules and then matches them up.  The main
problem is
> > that a class can be scheduled at different times on different days and
might
> > have different teachers teaching that same class because of the
scheduling.
> > Conceptually, it's still the same class, but the database can't see it
that
> > way.  OK.  here's the (relevant to this question) database structure we
have
> > so far:
>
>   I'm stopping before I read any farther.  You have these elements:
>
> Class  (ClassID, classname, otherclassinfo)
> teacher (TeacherID, Teachername, Otherteacherinfo)

Exactly

>  Now the question is how do we inter-relate these things
>
>  You say that you are dealing with the same class, but it may have
> different teachers and be on different days/nights.  Will it have the
> same students, or different students?

It will have the same students .. In essence, the students assigned to the
class define it being a particular class, but not always, so this can't be
considered an absolute.

>  I haven't experienced a class where the teachers changed from class to
> class.  So, I'm going to assume that you are dealing with two separate
> instances of a class, even though they are both CS101.  (did that make
> sense?)  There are two instances of CS101 being taught this semester.
> They are the same class, but each 'instance' has a different teacher
> and meets at different times.  Am I correct in that assumption?

Not quite .. Think of a gymnastics setting where you may have more than 1
teacher able to teach a class and more than 1 class available to each
teacher and students are added to such classes/teacher combinations, but the
students that are added depend upon the teacher/class combination.  In most
databases you have a 1-to-many, or a many-to-many ... well this is a
many-to-many-to-many relationship where any of the tables affects the
entries of the others.  What it boils down to is:

Teacher(s) + class + students(s) = done!

Make sense?

>  The instances of a class change every semester, but the basic
> circulum stays the same.
>
>  Based on that, our updated table structure:
>
> Class  (ClassID, classname, otherclassinfo)
> teacher (TeacherID, Teachername, Otherteacherinfo)
> ClassInstance (ClassInstanceID, ClassID, TeacherID)
>
>
>   The class instance table is strictly an intersection table.  Now we want
> to add our scheduling in there.  Presumably you do not want dates,
> but more of a 'monday / tuesday / wednesday' sort of thing.  Let's add
> in a schedule table:
>
> Class  (ClassID, classname, otherclassinfo)
> teacher (TeacherID, Teachername, Otherteacherinfo)
> ClassInstance (ClassInstanceID, ClassID, TeacherID)
> Schedule (ScheduleID, ClassInstanceID, ClassDay, ClassTime)
>
>   Now you can have multiple day/times for each instance of a class.
> You may even want to further normalize this, by separating out
> 'ClassDay' or 'Classtime' into separate tables.

This is where the problem is:

This still doesn't avoid the problem of the database needing to scheduling a
class and teacher and both together an infinite number of years/days/times
into the future.

Consider this related scenario:

You have students scheduled to attend a class.  Possibly, *any* teacher can
teach *any* class and *any* cl

Re: Database design question

[Jeffry Houser]

 I consider this type of topic my forte, so I hope I can help.
Although, not a CF-specific question, I think this definitely 
is something anyone should know before even starting to code in 
ColdFusion.  

> We are writing an application part of which handles scheduling of "classes"
> and "teachers" work schedules and then matches them up.  The main problem is
> that a class can be scheduled at different times on different days and might
> have different teachers teaching that same class because of the scheduling.
> Conceptually, it's still the same class, but the database can't see it that
> way.  OK.  here's the (relevant to this question) database structure we have
> so far:

  I'm stopping before I read any farther.  You have these elements:

Class  (ClassID, classname, otherclassinfo)
teacher (TeacherID, Teachername, Otherteacherinfo)

 Now the question is how do we inter-relate these things

 You say that you are dealing with the same class, but it may have 
different teachers and be on different days/nights.  Will it have the
same students, or different students?  

 I haven't experienced a class where the teachers changed from class to
class.  So, I'm going to assume that you are dealing with two separate 
instances of a class, even though they are both CS101.  (did that make 
sense?)  There are two instances of CS101 being taught this semester.  
They are the same class, but each 'instance' has a different teacher 
and meets at different times.  Am I correct in that assumption?  
 The instances of a class change every semester, but the basic 
circulum stays the same.

 Based on that, our updated table structure:

Class  (ClassID, classname, otherclassinfo)
teacher (TeacherID, Teachername, Otherteacherinfo)
ClassInstance (ClassInstanceID, ClassID, TeacherID)


  The class instance table is strictly an intersection table.  Now we want 
to add our scheduling in there.  Presumably you do not want dates, 
but more of a 'monday / tuesday / wednesday' sort of thing.  Let's add 
in a schedule table:


Class  (ClassID, classname, otherclassinfo)
teacher (TeacherID, Teachername, Otherteacherinfo)
ClassInstance (ClassInstanceID, ClassID, TeacherID)
Schedule (ScheduleID, ClassInstanceID, ClassDay, ClassTime)

  Now you can have multiple day/times for each instance of a class.
You may even want to further normalize this, by separating out 
'ClassDay' or 'Classtime' into separate tables.  

  Does any of this help, thus far?  I'm going to see about reading 
through the remainder of the e-mail to see if I can see where 
you are having trouble.  


> 
> Teacher
> 
> | primary key | bunch of other info |
> 
> Class Name
> 
> | primary key | class name |
> 
> Class Description
> --
> | primary key | bunch of other info |
> 
> Times
> --
> | primary key | year | day | hour |
> 
> Teacher Schedule
> --
> | primary key | teacher primary key | times primary key |
> 
> Class
> --
> | primary key | class name primary key | class description primary key |
> 
> Class Schedule
> ---
> | primary key | class primary key | times primary key |
> 
> Teacher and Classes Matched Up
> --
> | class schedule primary key | teacher schedule primary key |
> 
> It works like this:
> 
> Class Name + Class Description = Class
> 
> Class + Times = Class Schedule
> 
> Teacher + Times = Teacher Schedule
> 
> Class Schedule + Class Schedule = Matched Teacher with Class
> 
> or:
> 
> Class Name \
> Class --\
> Class Description ---/ \
>   Class Schedule \
> Times /   \
>   \
> Matched
>   Teacher Schedule ---/
> Teacher -/
> 
> The problem is with this design.  The rows in the Class Schedule and
> Teachers Schedule can be HUGE!
> After doing the math, and technically speaking, these rows, added together,
> could *possibly* = 678,000 rows.

  Without knowing the specific of the data your actually modeling, 
I can't really comment too much here.  How do you actually estimate 
the number of rows?  It looks as if you brought your level of normalization
a bit farther than I had done above (I.E. with class info in two separate 
tables, and the times in their own table).

> This is a good-sized number of rows for a database.   We are using a Sybase
> database, so I know that we can scale the equipment to handle this much
> info, but it is not cost effective, since we are looking at hosting this
> app. on our servers in an Application Service Provider capacity and are
> hoping to capture a large number of subscribers.  The cost of equipment to
> provide decent performance would be excessive, considering our price point.
> What I would like to know is, can anyone tell me a better way to design 

Database design question

[Todd Ashworth]

OK .. this isn't exactly a CF question, but it's related to a CF project,
and I'm not sure who else to ask.  DB gurus, please help :)

We are writing an application part of which handles scheduling of "classes"
and "teachers" work schedules and then matches them up.  The main problem is
that a class can be scheduled at different times on different days and might
have different teachers teaching that same class because of the scheduling.
Conceptually, it's still the same class, but the database can't see it that
way.  OK.  here's the (relevant to this question) database structure we have
so far:

Teacher

| primary key | bunch of other info |

Class Name

| primary key | class name |

Class Description
--
| primary key | bunch of other info |

Times
--
| primary key | year | day | hour |

Teacher Schedule
--
| primary key | teacher primary key | times primary key |

Class
--
| primary key | class name primary key | class description primary key |

Class Schedule
---
| primary key | class primary key | times primary key |

Teacher and Classes Matched Up
--
| class schedule primary key | teacher schedule primary key |


It works like this:

Class Name + Class Description = Class

Class + Times = Class Schedule

Teacher + Times = Teacher Schedule

Class Schedule + Class Schedule = Matched Teacher with Class

or:

Class Name \
Class --\
Class Description ---/ \
  Class Schedule \
Times /   \
  \
Matched
  Teacher Schedule ---/
Teacher -/

The problem is with this design.  The rows in the Class Schedule and
Teachers Schedule can be HUGE!
After doing the math, and technically speaking, these rows, added together,
could *possibly* = 678,000 rows.

This is a good-sized number of rows for a database.  We are using a Sybase
database, so I know that we can scale the equipment to handle this much
info, but it is not cost effective, since we are looking at hosting this
app. on our servers in an Application Service Provider capacity and are
hoping to capture a large number of subscribers.  The cost of equipment to
provide decent performance would be excessive, considering our price point.
What I would like to know is, can anyone tell me a better way to design this
database?

Many, many thanks in advance!!

Todd Ashworth


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
https://secure.houseoffusion.com

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Database Design Question

[Steve Runyon]

I wanted to add that if you're using Oracle,
it natively supports recursive queries without
resorting to a recursive or looping function.
I don't remember the syntax (I'm working with
SQLServer now and don't have any Oracle
references at hand), but you can write what
is basically a standard SQL statement with an
additional clause or two, and it will recurse
down through the layers of data.  You do have
to add a calculated field to determine what
your nesting level is, but it's pretty easy.

Good luck!


-Original Message-
From: Brook Davies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 20, 2000 7:42 PM
To: [EMAIL PROTECTED]
Subject: Database Design Question


I have listings that look like:

1.0 foobars
1.1 foobars the revenge
1.1.2 foobars the revenge II
1.1.2.1 more foobar specials
2.0 brown foobars
3.0 green foobars

etc..

What is the best DB design in terms of performance for a classification
system like this?

Thanks,
Brook Davies
MaracasMedia Inc.


--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Re: Database Design Question

[Dave Hannum]

Ben Forta wrote an SQL book last year - published by SAMS.  I think it's
Learn SQL in 10 Minutes.

Dave


=
"What we need is a list of specific unknown problems we will encounter"

David Hannum
Web Analyst/Programmer
Ohio University
[EMAIL PROTECTED]
(740) 597-2524



- Original Message -
From: Dan G. Switzer, II <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, June 21, 2000 10:04 AM
Subject: RE: Database Design Question


> Brook,
>
> I'd recommend buying SQL for Smarties by Joe Celko. There's a couple of
> chapter specifically on ways to handle hierarchy data w/different database
> systems. I really like the "Nested-Set Model." The nice thing about the
> Nested-Set model is that you can do all your tree parsing/order via SQL-no
> need to manipulate the data via CF (which you have to do when simply
storing
> the parent ID.)
>
> -Dan
>
> -Original Message-
> From: Brook Davies [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 20, 2000 7:42 PM
> To: [EMAIL PROTECTED]
> Subject: Database Design Question
>
> I have listings that look like:
>
> 1.0 foobars
> 1.1 foobars the revenge
> 1.1.2 foobars the revenge II
> 1.1.2.1 more foobar specials
> 2.0 brown foobars
> 3.0 green foobars
>
> etc..
>
> What is the best DB design in terms of performance for a classification
> system like this?
>
> Thanks,
> Brook Davies
> MaracasMedia Inc.
>
> --
--
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> --

> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: Database Design Question

[Dan G. Switzer, II]

Brook,

I'd recommend buying SQL for Smarties by Joe Celko. There's a couple of
chapter specifically on ways to handle hierarchy data w/different database
systems. I really like the "Nested-Set Model." The nice thing about the
Nested-Set model is that you can do all your tree parsing/order via SQL-no
need to manipulate the data via CF (which you have to do when simply storing
the parent ID.)

-Dan

-Original Message-
From: Brook Davies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 20, 2000 7:42 PM
To: [EMAIL PROTECTED]
Subject: Database Design Question

I have listings that look like:

1.0 foobars
1.1 foobars the revenge
1.1.2 foobars the revenge II
1.1.2.1 more foobar specials
2.0 brown foobars
3.0 green foobars

etc..

What is the best DB design in terms of performance for a classification
system like this?

Thanks,
Brook Davies
MaracasMedia Inc.


--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Re: Database Design Question

[Justin v0.9 MacCarthy]

Hi Brook,

Have a look at Joe Cleko's books SQL for Smarties  . He has lots on these
types of relationships IE trees.
Also there are lots of SQL functions examples to answer questions like

Path Enumeration
Finding root and "Leaf " nodes
Subtree deletetion
etc

IMO His books are a must have for "Real World SQL users" :-)

Checkem' out at :

http://www.amazon.co.uk/exec/obidos/ASIN/1558604324/qid=961587236/sr=1-1/026
-2836789-6296449
http://www.amazon.co.uk/exec/obidos/ASIN/1558603239/qid=961587236/sr=1-3/026
-2836789-6296449


~ JustinMacCarthy




- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, June 21, 2000 12:23 PM
Subject: RE: Database Design Question


> The standard way to take care of this is a self-referencing table, and a
> recursive function to bust them out.
>
> ID|Parent_ID
>
> Top levels have an id of 0, and the other rows work well with an
autonumber
> in the id.
>
> If children can have multiple parents, as in a BOM(Bill of Materials)
> problem, you'll need two tables of course.
>
>
> -Original Message-
> From: Brook Davies [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 20, 2000 6:42 PM
> To: [EMAIL PROTECTED]
> Subject: Database Design Question
>
> I have listings that look like:
>
> 1.0 foobars
> 1.1 foobars the revenge
> 1.1.2 foobars the revenge II
> 1.1.2.1 more foobar specials
> 2.0 brown foobars
> 3.0 green foobars
>
> etc..
>
> What is the best DB design in terms of performance for a classification
> system like this?
>
> Thanks,
> Brook Davies
> MaracasMedia Inc.
>
> --
--
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> --

> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
>

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: Database Design Question

[cbevis]

The standard way to take care of this is a self-referencing table, and a
recursive function to bust them out.

ID|Parent_ID

Top levels have an id of 0, and the other rows work well with an autonumber
in the id.

If children can have multiple parents, as in a BOM(Bill of Materials)
problem, you'll need two tables of course.


-Original Message-
From: Brook Davies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 20, 2000 6:42 PM
To: [EMAIL PROTECTED]
Subject: Database Design Question

I have listings that look like:

1.0 foobars
1.1 foobars the revenge
1.1.2 foobars the revenge II
1.1.2.1 more foobar specials
2.0 brown foobars
3.0 green foobars

etc..

What is the best DB design in terms of performance for a classification
system like this?

Thanks,
Brook Davies
MaracasMedia Inc.


--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Re: Database Design Question

[Dick Applebaum]

The typical way of defining this is:

   CREATE TABLE Category
(
 CategoryID Integer Identity Primary Key,
 DescriptionVarChar(255),
 ParentCategoryID   Integer References Category,
)

You will probably want to add an index on ParentCategoryID

Then you can expand it with something like:

   SELECT

  A.Description AS ADescription,
  B.Description AS BDescription,
  C.Description AS CDescription .

   FROM Category A, Catagory B, Category C .

   WHERE
 C.ParentCategoryID = B.CategoryID
 AND B.ParentCategoryID = A.CategoryID

This will provide a good combination of flexibility (good db design) and
performance.

If the the data are static, the number of rows small, and performance 
is critical then you might consider denormalization to gain 
additional performance.

HTH

Dick



At 4:42 PM -0700 6/20/2000, Brook Davies wrote:
>I have listings that look like:
>
>1.0 foobars
>   1.1 foobars the revenge
>   1.1.2 foobars the revenge II
>   1.1.2.1 more foobar specials
>2.0 brown foobars
>3.0 green foobars
>
>etc..
>
>What is the best DB design in terms of performance for a classification
>system like this?
>
>Thanks,
>Brook Davies
>MaracasMedia Inc.
>
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: Database Design Question

[Chris Montgomery]

Brook,

Check out this article on using recursion at the Defusion site:
http://www.defusion.com/articles/index.cfm?ArticleID=63

I've used this setup in my Amazon partner bookstore where I have
categories and subcategories.  Works well.  See
http://www.astutia.com/books/index.cfm for an example.

Chris Montgomery   [EMAIL PROTECTED]
:: Web development/Consulting  http://www.astutia.com ::
:: Allaire Consulting Partner/ NetObjects Reseller::
:: 210-490-3249/888-745-7603   Fax 210-490-4692   ::
:: Find a Job in San Antonio   http://www.sajobnet.com::


>-Original Message-
>From: Brook Davies [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, June 20, 2000 6:42 PM
>To: [EMAIL PROTECTED]
>Subject: Database Design Question
>
>
>I have listings that look like:
>
>1.0 foobars
>   1.1 foobars the revenge
>   1.1.2 foobars the revenge II
>   1.1.2.1 more foobar specials
>2.0 brown foobars
>3.0 green foobars
>
>etc..
>
>What is the best DB design in terms of performance for a
>classification
>system like this?
>
>Thanks,
>Brook Davies
>MaracasMedia Inc.
>
>---
>---
>Archives: http://www.eGroups.com/list/cf-talk
>To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Database Design Question

[Brook Davies]

I have listings that look like:

1.0 foobars
1.1 foobars the revenge
1.1.2 foobars the revenge II
1.1.2.1 more foobar specials
2.0 brown foobars
3.0 green foobars

etc..

What is the best DB design in terms of performance for a classification 
system like this?

Thanks,
Brook Davies
MaracasMedia Inc.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.