<CFSET NEWBIE= "ME"> <CF_BEG_FOR_H

[tlieu]

<CFSET NEWBIE= &quot;ME&quot;>
<CF_BEG_FOR_HELP>

<CF_MY_TABLES>
*Table &quot;Students&quot; with columns &quot;AthleteID and &quot;Password&quot;
*Table &quot;Profile&quot; with column &quot;ProfileID&quot; and numerous columns that contain info about athletes.
*Table &quot;Coaches&quot; with columns &quot;CoachID&quot; and &quot;Password&quot;

<CF_GOAL>
I want an application that will allow coaches to browse through the athletes table then save the profiles they want to their drawer. The coaches can save up to 10 profiles.

<CF_THEORY>
I'm thinking the best way to go about this would be to make a table called &quot;Drawer&quot; with a DrawerID = CoachID; thus each drawer belongs to a certain coach. The table would then have 10 additional columns that correspond to the number of athlete profiles the coach can save.

So when a coach saves a profile, a link to the profile is saved to a column ( numbered 1 - 10 ). If all the columns are filled, then the coach is prompted to manage his drawer.

<CF_QUESTION>
My question is how to create a query that will output columns and that can be edited?

In my appication the coach logs on and his session.coachID is recorded.

A row is created with the drawerID = session.coachID when he saves a link to a profile. The link to the profile is then saved to the 1st column. So he has 9 columns left. The next time he saves, how do I get the app to input to the second column? and how do I recordcount the columns so I can keep the coach from resaving a profile that is already in his drawer?

<CF_THANKS>
Any help would be GREATLY APPRECIATED! Thanks in advance!




Table &quot;Drawer&quot; with columns

 

[randall2nd]

You could try using an array.

when the coach logs in, use his/her coachID to pull the coresponding row(drawer) from the drawer table. Load the columns into an array. You can then just check the length of the array. And when you save just use the array to put the info back into the database.


anthoer way of doing this but might make for a long table is to just make your table drawer with two columns[coachID, playerID] you can then just pull all the rows for a certain coach and check the recordcount on that query.

Hope this helped a little Randall2nd

 

[josel]

As a programmer, I normally ask millions of questions but I will ask just a few for you to concider additional database design thus possibly avoiding problems down the row ...

QUESTIONS:
Q. Can an athlete be in two or more drawers?
- NO. Then I would put CoachID within Atlete table
- YES. Then I would do as you say, use a Drawer table

Q. Can a coach pull athlete from his drawer (release athlete)?

Q. What if athlete leaves campus?

Q. Are athletes available ONLY if they meat sertain criteria? If so, where is this kept?

I would spend a bid of time figuring short and long term needs prior to determining how data will be edited. There are many ways to edit records or content there of, but the key to a successfull application lies on its foundation.

Time used to outlined everything you need and allowing space for growth is time well spent. I find it hard to suggest the particular method for you to employ as true nature of the progject is not fully outlined. I would suggest you buy &quot;SkillBuilding with Coldfusion&quot; and a book I've read &quot;Essential ColdFusion&quot;. Within these, I am sure you will find your answers and then some!

This post is NOT intended to offer an answer to your question, but more to provide you with food for thought which I hope help you in the long run.

Good luck!

josel

PS: I am a newbie myself, as far as CF is concerned. While &quot;arrays&quot; are, without a doubt, good for things such as this, they are hardly anything a newbie can handle unless you have experience with other programs which employ arrays in the same form as CFML.




If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[calista]

OK, my $.02 worth. Do a little reading about relational databases. I confess I didn't care much for database class in school, but it came in handy when it came time to my database.

I have a situation similar to yours. I have a database of documents and products. Any given document may (and probably does!) go with more than one product, and any given product, of course, has many documents associated with it. Therefore, the products and the documents have a many-to-many relationship. But, for the purposes of your database, you want a one-to-one or one-to-many relationship. The way I handled this is by having a third table, the ProductDocument table that is a bridge between the Product table and the Document table. Each Product and each Document has a unique ID that is called the primary key. The primary key in the ProductDocument table is a combination key of the Product ID and the Document ID. So, one document that goes with three products will have three entries in the ProductDocument table. The Document ID portion will be the same for each entry, but the Product ID will be different for each entry, giving me a unique primary key in the ProductDocument table. If I want all the documents for a given product, I first query the PD table for all entires that have that Product ID as part of the key. From those results, I query the Document table to get the details about the documents. Get the idea?

Jose is right, this is the time to think very carefully about what you are doing. Time spent now planning your database will be time well spent. Calista :-X
Jedi Knight,
Champion of the Force

 

[tlieu]

Well, I finally got the darned thing working before I came back to check. I'm DEEPLY appreciative of the responses I've gotten; the suggestions and comments opened my horizons though--I'm certainly going to be looking more at your suggestions.

Anyhow, what I ended up doing was:
TABLE 1: StudentID, Firstname, Lastname, etc.
TABLE 2: CoachID, Firstname, Lastname
TABLE 3: DrawerID, StudentID, CoachID

I joined tables 1 and 3 and then check for any matches where 2.CoachId = or <> 3.CoachID to determine if an athlete was in a particular coach's drawer. I then did a recordcount of the joined table to using similar principles to see if their drawers were filled to capacity or not.

Now I'm just curious about the performance issues of joining tables.