I have learned the hard way when updating a field in a view that doesn't use a primary key and several records all get changed. So I feel confident on that part but with all the discussions on candidate vs unique index makes me believe I don't understand the difference.
At the point where I generate the billing, the index I want in place would not allow an insert command to duplicate a billing for that student/class/session. I just assumed unique meant only 1 allowed per table. Gary Jurink -----Original Message----- From: Ted Roche [mailto:tedro...@gmail.com] Sent: Wednesday, April 24, 2013 2:51 PM To: profox@leafe.com Subject: Re: primary or unique index On Wed, Apr 24, 2013 at 3:08 PM, Gary Jeurink <g.jeur...@charter.net> wrote: > My database training from books leaves me with many questions. I am > building > a billing table so I have a transID (auto incr), studentID (int), classID > (int), clientID (int.. who is billed), cost (float), sessionID (int) and > sessYear (char or int). Now I need a unique compound index of studenID, > classID, sessYear, and sessionID so that a client can only be billed once > for attending that class. Million dollar question, how do I do that? . > > something tells me that the transID is unnecessary but experience in fox > pro > tells me that in order to be able to update this record in a view I must > have the transID as->key in order to specify the updatable fields. > > Not only does FoxPro make life easier if you go this way, it is also the best thing to do: all tables should have a non-data-bearing primary key. It eliminates an entire class of problems with having to update the key when some other business requirement needs it to be different, because it is by definition non-data-bearing. So, yes, have a transID. The rest of the keys seem to be too many, imo. Doesn't the combination of keys (student, classID, SessYear, SessionID) make up the "spots" record? If so, you should just be using a spotsID here. The key idea of normalization is not to have the same piece of information in more than one place. So, if there is a unique set of keys that always occur together, they should be in one place and one place only, and the key to _that_ record is used to represent that unique set. Hope this is clear. My full lecture on normalization goes on for well over an hour (see http://www.tedroche.com/papers.php, under '2004' - the Data Design presentation) and I'll try to spare you that. Meanwhile, back at your question: INDEX ON STR(StudentID,12)+STR(classID,12)+STR(sessYear,12)+STR(sessionID,12) TAG NoDupes CANDIDATE (ref: http://msdn.microsoft.com/en-us/library/9x4e2b05(v=vs.80).aspx) (Adjust the 12 as needed for field lengths and maximum values, but always specify the lengths of strings in indexes. Otherwise, bad things have been known to happen.) --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/D87641F6EABB4F56973BB8FF44D458F9@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
