Re: Database design question
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 cf-talk@houseoffusion.com Sent: Monday, August 21, 2006 3:33 PM Subject: Re: Database design question - Original Message - From: Michael E. Carluen [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com 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
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
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
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
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: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
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 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 cf-talk@houseoffusion.com 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
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 cf-talk@houseoffusion.com 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
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 cf-talk@houseoffusion.com 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
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 cf-talk@houseoffusion.com 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
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 cf-talk@houseoffusion.com 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
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 cf-talk@houseoffusion.com 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
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=archiveyear=2006month=6day=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 cf-talk@houseoffusion.com 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
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
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 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
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
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
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
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
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
RE: Database design question
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
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=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=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
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
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
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
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=subscribeforumid=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
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=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
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=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: database design question re:product price changes
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: database design question re:product price changes
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
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
RE: Database Design Question
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
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
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
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
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
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]
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
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: database design question
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 -- 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
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
RE: database design question
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
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
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
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
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
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 this database? I've given you my input up above.. Better or
Re: Database design question
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* class can be held on *any* day at *any* hour (or 1/6 there-of) during *any* year .. infinitely! Conceptually, the class is the same class. Our human minds can easily figure this out. But, how do we represent this in a database? Example: Class1 meets Mon and Wed from 4-6 and Thurs. from 6 - 7:15 It is taught Mon and Thurs by Bob and Wed. by Sa
RE: Database Design Question
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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Database Design Question
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=listsbody=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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Database Design Question
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=listsbody=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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Database Design Question
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=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
