If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message oups.com... On Aug 20, 4:50 pm, "BruceM" wrote: I think you are saying that the PK designation need not be used. In any case, I still don't get how a combination of fields that includes values subject to change (such as End_Date becoming not null) would be used in relationships. I think I get what you are saying about candidate keys and a number of other related things, but I still don't get how relationships are modeled. We have a minor language issue. You seem to use "relationship" to mean "Access Relationship with RI enforced", whereas I would use "FOREIGN KEY" or perhaps "referenced table" and "referencing tables" in context. FWIW I model a relationship using a table which I call a "relationship table" which IIRC you call a "junction table". I don't necessarily mean with RI enforced, but in most table relationships that is the case. A join in a query may not have RI enforced. When I refer to a table relationship I am using Access terminology to mean just what you surmise. Let's say tblStudent contains student information, and tblCourse contains Course information. Each Student can be in many Courses, and each Course contains many Students, so tblStudentCourse resolves the many-to-many relationship. I call tblStudentCourse a junction table. A junction table in my lexicon is this specific type of table. So, I'm assuming you are referring to a FOREIGN KEY (my term). You are correct that the NULL value here is a complication and one worth avoiding by design. In Access/Jet, NULL and non-NULL values in the referencing table must match exactly in the referencing table by treating the NULL value as if it were an actual value; while this is not strictly the correct way to handle the NULL value, it has utility because the non-matching non-NULL values will cause the FK to bite, also altering the NULL value to a non-NULL value in the referencing table will result in the new value being CASCADEd to the referencing table (assuming the ON UPDATE CASCADE referential action has been specified for the FK). Contrast this with SQL Server which will not test any FK rows involving the NULL value (i.e. the non-NULL values may not match but the still FK will not bite as it would in Access/ Jet) and changing from the NULL value to a non-NULL value does not CASCADE; arguably more correct but has less utility. I'm finding it hard to describe long hand so here's an example (sorry its ANSI-92 Query Mode and includes the dreaded DDL): CREATE TABLE Test1 ( col1 INTEGER NOT NULL, col2 INTEGER, UNIQUE (col1, col2) ) ; CREATE TABLE Test2 ( col1 INTEGER NOT NULL, col2 INTEGER, FOREIGN KEY (col1, col2) REFERENCES Test1 (col1, col2) ON DELETE CASCADE ON UPDATE CASCADE ) ; INSERT INTO Test1 (col1, col2) VALUES (1, NULL) ; INSERT INTO Test2 (col1, col2) VALUES (1, NULL) ; Consider this query: SELECT * FROM Test1 AS T1 INNER JOIN Test2 AS T2 ON T1.col1 = T2.col1 AND T1.col2 = T2.col2; returns no rows because T1.col2=T2.col2, NULL=NULL, causes the row to be removed from the resultset; put crudely, you can't compare the NULL value to anything, even the NULL value. I cannot get the CREATE TABLE stuff to work, even though I am using the correct query mode. It expects a line number or a label or a few other things. However, I think I see what you are getting at. Now consider this: DELETE FROM Test1; causes the row in Test2 to be removed; Access/Jet successfully matched NULL=NULL. Impressive or no? In SQL Server, the row would remain (not orphaned because it was never considered to be referencing in the first place). That's one of the reasons why I used a NOT NULL end_date when I posted the example i.e. to avoid this complexity ...but you did ask Regardless, I would expect any designer to use (employee_number, start_date) as the key for referencing tables. So PRIMARY KEY is not really a primary key, and the use of a field in a composite PRIMARY KEY that is not really a primary key is about clustering rather than uniqueness? Do you really wonder that I am having trouble following your points? I've tried to show you tables with a PRIMARY KEY but no logical key, tables where a PRIMARY KEY cannot prevent duplicate data, and tables where PRIMARY KEY is used for purposes of physical indexing rather than logical keys. I am lost with the difference between PRIMARY KEY and primary key, although I suspect you are referring to the difference between a logical key (the combination of fields that assures the record is unique) and a key that is used in relationships with other tables (since a dozen fields may be needed to construct a suitable logical key). However, as I have explained several times I ensure by one means or another that records are unique. I have asked how one places a unique constraint on a combination of fields without making them into an Access PK. I don't know much about using a database engine other than Jet, so the differences with a SQL engine are lost on me. If you have answered my question about multi-field keys, I could not understand your response as an answer to the question. When you choose to use PRIMARY KEY, the choice of what you use it for it arbitrary; feel free to omit a PRIMARY KEY but be ready for someone to challenge you on it (e.g. document your reasons) because there is this 'touch stone' in SQL that every table should have a primary key/ PRIMARY KEY -- I really don't know what variety they mean! Personally, I think every table should have a both a primary key and a PRIMARY KEY, basing their choice for the latter on good (documented) reasons. For Access/Jet, clustering is IMO the best reason for using PRIMARY KEY but I do know that many people value that bold text in the Access 'Relationships' diagram. Final point on this subject: if someone is unaware of Access/Jet's clustering behaviour, can they really make an informed decision about PRIMARY KEY designation? No idea. I know that my databases work, and it's not just dumb luck. I would limit the user choice in the front end to vehicles of the appropriate category. When "SUV" is selected, a combo box from which to select the model would show only SUVs. Do I need to tell you that your approach doesn't actually solve the problem at hand? The aim is to prevent this (aircode): INSERT INTO SUVs (VIN, etc) SELECT VIN, etc) FROM Vehicles WHERE vehicle_type = 'Sedan'; I prevent it by limiting the choices the user can make once SUV has been selected. and similar updates that are contrary to the business rules. What's your position on PRIMARY KEY? If I showed you this table: CREATE TABLE Employees ( employee_number INTEGER ); and pointed out that all nine front end programs known to use the database have front end code to trap duplicates and NULLs, would you think it was a good idea to omit a NOT NULL unique constraint from the SQL table? I would tend not to edit employee information in those databases. When I need to edit the information I go to the database that contains that information. That is to say, I have one way to edit the information. Anyhow, I *never, ever* argued in favor of allowing nulls in required fields. If I am using the Employee table to populate a combo box list from which a name is selected, there is no way to select a Null value. I can require that the field be filled in before the rest of the record can be completed by means of front end code to enforce the fact that the employee ID is required in the table. Or I can use validation at the table level, although that is not my usual choice. Jamie. -- |
#42
|
|||
|
|||
How do you mulitply in a field?
On 21 Aug, 13:43, "BruceM" wrote:
I have asked how one places a unique constraint on a combination of fields without making them into an Access PK. I don't know much about using a database engine other than Jet, so the differences with a SQL engine are lost on me. If you have answered my question about multi-field keys, I could not understand your response as an answer to the question. ALTER TABLE EmployeeSalariesHistory ADD CONSTRAINT my_constraint_name UNIQUE (employee_number, start_date); This will work for Jet in ANSI-92 Query Mode Jet SQL. It also happens to be standard SQL so has a very good chance of working in any given SQL DBMS. You can do the same in the Access user interface by create an index, choosing the fields and specifying 'allow duplicates = no' (or similar; haven't got Access on this machine). if someone is unaware of Access/Jet's clustering behaviour, can they really make an informed decision about PRIMARY KEY designation? No idea. I know that my databases work, and it's not just dumb luck. An mdb doesn't need indexes to work but they can improve performance. Why miss out on the opportunity of a physically ordered index? BTW when I say 'indexes' I mean the 'allow duplicates = yes' kind i.e. the ones for performance and nothing else. I prevent it by limiting the choices the user can make once SUV has been selected. I open up Excel, create a new MSQuery session, connect to your mdb, and execute this from it's SQL window: INSERT INTO SUVs (VIN, etc) SELECT VIN, etc) FROM Vehicles WHERE vehicle_type = 'Sedan'; How has your application 'limited my choices'? I now have 'sedans' in my SUVs table! What's your position on PRIMARY KEY? If I showed you this table: CREATE TABLE Employees ( employee_number INTEGER ); and pointed out that all nine front end programs known to use the database have front end code to trap duplicates and NULLs, would you think it was a good idea to omit a NOT NULL unique constraint from the SQL table? I *never, ever* argued in favor of allowing nulls in required I think you missed my point. You said earlier you would trust your front end controls to prevent 'sedans' from being entered into the 'SUVs' tables. Because I consider this unacceptable myself (data integrity being a job for the data layer IMO), I was wondering whether you think a simple unique constraint (or PK if you like) can also be trusted to the front end application or nine front end applications in this case. What if one of those applications had a bug in their duplicate testing routine? Personally, I'd want a constraint in the DBMS to trap such 'data' bugs. Jamie. -- |
#43
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message
oups.com... On 21 Aug, 13:43, "BruceM" wrote: I have asked how one places a unique constraint on a combination of fields without making them into an Access PK. I don't know much about using a database engine other than Jet, so the differences with a SQL engine are lost on me. If you have answered my question about multi-field keys, I could not understand your response as an answer to the question. ALTER TABLE EmployeeSalariesHistory ADD CONSTRAINT my_constraint_name UNIQUE (employee_number, start_date); This will work for Jet in ANSI-92 Query Mode Jet SQL. It also happens to be standard SQL so has a very good chance of working in any given SQL DBMS. You can do the same in the Access user interface by create an index, choosing the fields and specifying 'allow duplicates = no' (or similar; haven't got Access on this machine). OK, thanks. That led me on an investigation I might not have made otherwise. if someone is unaware of Access/Jet's clustering behaviour, can they really make an informed decision about PRIMARY KEY designation? No idea. I know that my databases work, and it's not just dumb luck. An mdb doesn't need indexes to work but they can improve performance. Why miss out on the opportunity of a physically ordered index? Who said I avoid indexes? I have used them on single fields only, but I can see the value of indexing on a combination of fields (although maybe not always a unique combination, for instance with FirstName and LastName fields). BTW when I say 'indexes' I mean the 'allow duplicates = yes' kind i.e. the ones for performance and nothing else. I prevent it by limiting the choices the user can make once SUV has been selected. I open up Excel, create a new MSQuery session, connect to your mdb, and execute this from it's SQL window: INSERT INTO SUVs (VIN, etc) SELECT VIN, etc) FROM Vehicles WHERE vehicle_type = 'Sedan'; How has your application 'limited my choices'? I now have 'sedans' in my SUVs table! I apply security so that the database can't be hacked. What's your position on PRIMARY KEY? If I showed you this table: CREATE TABLE Employees ( employee_number INTEGER ); and pointed out that all nine front end programs known to use the database have front end code to trap duplicates and NULLs, would you think it was a good idea to omit a NOT NULL unique constraint from the SQL table? I *never, ever* argued in favor of allowing nulls in required I think you missed my point. You said earlier you would trust your front end controls to prevent 'sedans' from being entered into the 'SUVs' tables. Because I consider this unacceptable myself (data integrity being a job for the data layer IMO), I was wondering whether you think a simple unique constraint (or PK if you like) can also be trusted to the front end application or nine front end applications in this case. What if one of those applications had a bug in their duplicate testing routine? Personally, I'd want a constraint in the DBMS to trap such 'data' bugs. When I select an Employee in one of those nine databases, the number only gets stored. If I am selecting only one name (number), the duplicates issue does not occur. If I am selecting subform records (attendees at a training session or something) then I can see the point to a multi-field index in the child table, consisting of the EmployeeID together with the table's key field. However, I don't want it ever to get to the point of the user seeing the built-in error message that results from accidentally selecting the same name twice. I'm glad finally to understand the distinction between multi-field index and multi-field PK. |
#44
|
|||
|
|||
How do you mulitply in a field?
On 21 Aug, 18:07, "BruceM" wrote:
if someone is unaware of Access/Jet's clustering behaviour, can they really make an informed decision about PRIMARY KEY designation? No idea. I know that my databases work, and it's not just dumb luck. An mdb doesn't need indexes to work but they can improve performance. Why miss out on the opportunity of a physically ordered index? Who said I avoid indexes? I'm trying to draw a parallel. Consider I pointed out to someone that I thought they'd get better performance if they indexed certain columns and they replied, "I know that my database works, and it's not just dumb luck." Yes, 'works' trumps 'optimized' but why not have both? I have used [indexes for performance] on single fields only, but I can see the value of indexing on a combination of fields (although maybe not always a unique combination, for instance with FirstName and LastName fields). You put your physically ordered index -- arguably your best index , your uber-index -- on an autonumber column, which is unique *and* meaningless, right? And you do this because you value the fact it appears as bold text in the Relationship diagram to indicate that it should be used in a foreign key and joins, right? It's your choice of course, and it's an arbitrary one, and you can decide to ignore clustering, as is your prerogative as designer. But another designer who is *unaware* of clustering, can we say they've made an informed decision? I open up Excel, create a new MSQuery session, connect to your mdb, and execute this snipped I apply security so that the database can't be hacked. Is this another case where I have to come up with an example that exactly fits before you can see the point I'm trying to make? Can't you cut me a little slack g? I'm not referring to malicious attacks. It is common for a user of an Access application to have privileges on objects in the back end database and in my experience it is almost inevitable that the objects will at some point be queried using something other than your Access application; I'd suggest Excel is the tool of choice for this (take a look in the Excel groups), followed closely by linked tables in another mdb. You've never encountered this? Never mind. It could be you have a way of ensuring that only your Access application has privileges on the database; if so, please share your techniques. Allow me to me try another angle. Consider this VBA code in the proverbial 'Vehicles' Access app (the one will all the exclusive privileges, say): ' Create some dynamic SQL strSQL = "INSERT INTO SUVs (VIN)" & _ strSQL = strSQL & " SELECT VIN) FROM Vehicles" ' Limit to SUVs only sSQL = sSQL & " WHERE vehicle_type = 'SUV';" ' Update the table Connection.Execute strSQL Oops! The coder appended the search condition to the wrong variable, one will a similar name. If it was my database I'd want a validation rule at the data engine level that would highlight the error to the coder pretty early on, otherwise my database is open to 'attack' from any bug that gets introduced into the front end(s). In my land we call this, "Belt and braces," and I've heard Mr Celko call it, "Mop the floor and fix the leak." But if you are only prepared to do one thing then I suggest it should be done as close to the data as possible i.e. engine-level constraints (FWIW a CLR rules engine shared by SQL Server database engine and front end is appealing). And if you'd do entity type checking in the front end, why not do your unique indexes and FKs in the front end too?! Jamie. -- |
#45
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message oups.com... On 21 Aug, 18:07, "BruceM" wrote: if someone is unaware of Access/Jet's clustering behaviour, can they really make an informed decision about PRIMARY KEY designation? No idea. I know that my databases work, and it's not just dumb luck. An mdb doesn't need indexes to work but they can improve performance. Why miss out on the opportunity of a physically ordered index? Who said I avoid indexes? I'm trying to draw a parallel. Consider I pointed out to someone that I thought they'd get better performance if they indexed certain columns and they replied, "I know that my database works, and it's not just dumb luck." Yes, 'works' trumps 'optimized' but why not have both? Did you notice I said I can understand the value of a multi-field index, now that I understand the distinction you are making between an index and a primary key (as the term is used in Access)? I do not know about clustering behavior. I don't know if you are referring to PRIMARY KEY or primary key, or what the distinction is. I can't make an informed decision about the air pressure in my car's tires, either, because I don't understand the physics involved. However, people who know about such things have suggested the correct air pressure. I don't give it a lot of thought beyond that. I have used [indexes for performance] on single fields only, but I can see the value of indexing on a combination of fields (although maybe not always a unique combination, for instance with FirstName and LastName fields). You put your physically ordered index -- arguably your best index , your uber-index -- on an autonumber column, which is unique *and* meaningless, right? And you do this because you value the fact it appears as bold text in the Relationship diagram to indicate that it should be used in a foreign key and joins, right? NO! You are projecting something I never so much as hinted at. I use it because it doesn't change, and because I prefer the simplicity of a single-field join where possible. This is not about the convenience of bold type. It's your choice of course, and it's an arbitrary one, and you can decide to ignore clustering, as is your prerogative as designer. But another designer who is *unaware* of clustering, can we say they've made an informed decision? I open up Excel, create a new MSQuery session, connect to your mdb, and execute this snipped I apply security so that the database can't be hacked. Is this another case where I have to come up with an example that exactly fits before you can see the point I'm trying to make? Can't you cut me a little slack g? I'm not referring to malicious attacks. Again, I apply security so that the database cannot be changed outside of the parameters I choose as the developer. The security can be circumvented by a determined and knowledgeable user, but for my situation it is adequate. It never occurred to me that somebody would take the Excel or some such similar route, which sounds to me like a skilled and determined attempt to undermine the data integrity. If the user can get through the security, no approach to indexing is going to help. The answer, I'm afraid, is that I don't see your point. It is common for a user of an Access application to have privileges on objects in the back end database and in my experience it is almost inevitable that the objects will at some point be queried using something other than your Access application; I'd suggest Excel is the tool of choice for this (take a look in the Excel groups), followed closely by linked tables in another mdb. You've never encountered this? Never mind. It could be you have a way of ensuring that only your Access application has privileges on the database; if so, please share your techniques. Allow me to me try another angle. Consider this VBA code in the proverbial 'Vehicles' Access app (the one will all the exclusive privileges, say): ' Create some dynamic SQL strSQL = "INSERT INTO SUVs (VIN)" & _ strSQL = strSQL & " SELECT VIN) FROM Vehicles" ' Limit to SUVs only sSQL = sSQL & " WHERE vehicle_type = 'SUV';" ' Update the table Connection.Execute strSQL Oops! The coder appended the search condition to the wrong variable, one will a similar name. If it was my database I'd want a validation rule at the data engine level that would highlight the error to the coder pretty early on, otherwise my database is open to 'attack' from any bug that gets introduced into the front end(s). You know, I have actually tried to agree with you on some points. I understand the value a multi-field index can have. I added that I want the user to be informed by words of my choosing rather than default error messages. I want to intercept the problem before then. The front end is for the user's convenience, and I want it to be as convenient as is possible. In my land we call this, "Belt and braces," and I've heard Mr Celko call it, "Mop the floor and fix the leak." But if you are only prepared to do one thing then I suggest it should be done as close to the data as possible i.e. engine-level constraints (FWIW a CLR rules engine shared by SQL Server database engine and front end is appealing). See my previous comment. And if you'd do entity type checking in the front end, why not do your unique indexes and FKs in the front end too?! Because there are no tables in the front end. Jamie. -- |
#46
|
|||
|
|||
How do you mulitply in a field?
Agree? Not really, other than we all seemed to agree that a resultset (my term) does not need to be normalized. I am surprized you do not agree. A VIEW is a 'table' by SQL-92 definitions, and a 'table' is 'a multiset of rows' by SQL-92 definition, and 'a multiset of rows' is a 'resultset' ... admittedly the definition of a 'resultset' is my interpretation based on context through out the thread. With that line of thought, a VIEW is, by definition, a 'resultset', which does not need to be normalized, as we agreed. If you do not agree to that, then, how 'bout this ... A VIEW is, by SQL-92 definition is a 'derived table' ... and 'derived tables' should not be forced to follow normalizton guidlines. You seem to be saying that normalization can be applied to SQL objects but only SQL base tables; I've agreed this can only be done *informally*. Yes ... I generally say statements like "My tables are normalized", (in the context of that statement, I mean for 'table' to be analogous to 'base table' from SQL-52 I'm saying that if you choose to apply normalization to SQL base tables, as many people do in the groups, then you should apply it equally to SQL viewed tables (VIEWs); as a practical approach I've suggested that if your VIEW would not 'pass muster' as a normalized table then alter it to become the resultset of a stored procedure and that will prevent it being used as a table (being queried as a table, SQL updates as a table) -- and you seem to disagree with all this! It is my opinion that if an SQL Procedure returns a 'resultset', then its return type is 'table', and since the 'resultset' is derived, it does not need to be normalized. Prehaps it's time to "agree to disagree" As per norm, I am typically agreeable! ... and in our discussions, I beleive we have both shown familiarity with the topic at hand, so ... if the words of my posts have not changed your view on the normalization, or lack of normalizaton, for VIEWS, then perhaps it is time to agree and say ... "good discussion!" .. with respect of course! See ya! Brent Spaulding | datAdrenaline "Jamie Collins" wrote in message oups.com... On Aug 20, 9:57 pm, datAdrenaline wrote: So ... with these definitions a VIEW is a table, but a table is not a base table, and thus, a VIEW is not required to meet the set informalized rules for normalization implied by the statement: "Are your tables normalized?" Which should be properly stated (can there be proper imformal?) ... "Are your base tables normalized?" ... So, as I stated to Jamie in an individual reply, we have spent all this time pegging the geek meter ... proving that we are truly not- normal, by discussing something in depth to which we all seem to broadly agree. Agree? Not really, other than we all seemed to agree that a resultset (my term) does not need to be normalized. You seem to be saying that normalization can be applied to SQL objects but only SQL base tables; I've agreed this can only be done *informally*. Graham R Seach seems to be saying that normalization cannot be applied to SQL objects; while I've agreed this is the correct *formal* approach and I've tried to clarify whether he would permit it informally (as the person I was originally replying to had done) but he has not replied. I'm saying that if you choose to apply normalization to SQL base tables, as many people do in the groups, then you should apply it equally to SQL viewed tables (VIEWs); as a practical approach I've suggested that if your VIEW would not 'pass muster' as a normalized table then alter it to become the resultset of a stored procedure and that will prevent it being used as a table (being queried as a table, SQL updates as a table) -- and you seem to disagree with all this! Prehaps it's time to "agree to disagree" Jamie. -- |
#47
|
|||
|
|||
How do you mulitply in a field?
I said SQL-52 ... I meant SQL-92 ....
-- Brent Spaulding | datAdrenaline |
#48
|
|||
|
|||
How do you mulitply in a field?
On 22 Aug, 14:00, "BruceM" wrote:
I do not know about clustering behavior See: New Features in Microsoft Jet Version 3.0 http://support.microsoft.com/kb/137039 "New compacting method. Compacting the database now results in the indices being stored in a clustered-index format. While the clustered index isn't maintained until the next compact, performance is still improved... The new clustered-key compact method is based on the primary key of the table." Defragment and Compact Database to Improve Performance http://support.microsoft.com/kb/288631 "If a primary key exists in the table, compacting restores table records into their primary key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient." I can't make an informed decision about the air pressure in my car's tires, either, because I don't understand the physics involved. However, people who know about such things have suggested the correct air pressure. I don't give it a lot of thought beyond that. Here's the bare bones: you've have this thing (PRIMARY KEY) which fulfils two main functions (null-free uniqueness and clustering respectively) where one function sometimes compromises the other (a sole unique column makes a poor cluster; multiple columns makes a clustered compound index look like a verbose key) but you are also given a second thing (NOT NULL UNIQUE) which performs one of the functions (null-free uniqueness), which leaves the other function (clustering) exclusive to the first thing (PRIMARY KEY). I can't think of anything car tire/tyre pressure related that fits the above so try this: Onedaywhen Cottage has central heating with zones, each zone has a portable digita thermostat with integral clock. Mrs Onedaywhen likes to keep the bedroom zone's thermostat on her bedside table/nightstand because it makes a handy bedside clock. I know enough to deduce she's chosen a lousy position for the thermostat e.g. the heat from the bedside light sometimes causes the heating to switch off prematurely. "Here," I say, "If you want to tell the time, use this alarm clock. Then we can put the thermostat in a more appropriate location." But we're at an impasse because people who know about such things have suggested to her that the clock's timekeeping is accurate by design because the boiler needs to know when to come on and she does not understand the physics of printed circuit boards and oil- fired boilers. But I keep telling her, "It's not just a clock, it's a thermostat too. Use something else to tell the time." She replies, "I do not know about thermostats." I've now given her some links to articles on the manufacturer's website so the ball's in her court. Again, I apply security so that the database cannot be changed outside of the parameters I choose as the developer. The security can be circumvented by a determined and knowledgeable user, but for my situation it is adequate. It never occurred to me that somebody would take the Excel or some such similar route, which sounds to me like a skilled and determined attempt to undermine the data integrity. If the user can get through the security, no approach to indexing is going to help. The answer, I'm afraid, is that I don't see your point. Again, I'm not referring to malicious attacks. If you give people permissions on database object then in my experience sooner or later someone will think it's a good idea to access those objects via Excel e.g. to help them do their job better. This doesn't give them the right to act irresponsibly, though. Perhaps they thought it would be OK to correct some data directly in the table and are unaware you have some data integrity correction code in one of your forms that has now been inadvertently circumvented. If I've been given a key to the stationary cupboard and I need to sign some paper copy then I'll help myself to a pen. I still know that having a key doesn't mean it's OK for me to go home today with ten pens in my pocket. If you don't want me to have one of the pens, take away my key; if you want to allocate pens yourself, keep them locked in your desk drawer so that I *must* go through you to get a pen. It's pretty clear that picking locks is illicit behaviour. But if I've used my key to acquire a pen for my work, I don't see myself as having successfully made a skilled and determined attempt to undermine your stock control system -- I'm just trying to do my job. Perhaps there is some middle ground e.g. you provide communal pens in handy locations *but* keep them attached to chains, like they do in banks, so that I don't do my usual trick of sticking it behind my ear then forgetting and walking off with it. Providing a SQL PROC or VIEW in the SQL DBMS is the provision of pens (i.e. facilities me in doing my work without too much inconvenience), and integrity constraints and data validation rules at the data engine level are the chain (i.e. stops me messing up the system for my own sake and others). Saying I have to always use your application's forms is like saying I have to always use your pen, IMO too restrictive: what if your pen has the wrong color/colour ink, doesn't work as well as I like, you're not in the office today etc. And before you got upset that I found a pen myself, had you mentioned that you are considered to 'own' all the pens? If you did mention it, why do I have a key to the pens cupboard? Again, if you've found a way of granting privileges to the application rather than to users (the latter being the most commonly seen scenario) then please share your techniques, there is an Access MVP in another thread wanting such information. I want the user to be informed by words of my choosing rather than default error messages. I want to intercept the problem before then. The front end is for the user's convenience, and I want it to be as convenient as is possible. Me too. Why not trap failures in the front end and in the data engine? i.e. attempt to trap validation rules violations in the front end but also have a validation rules in the data engine as a last line of defence against things the front end guy missed, VBA code bugs, etc. Errors generated by the engine can be caught so even when the front end does encounter an 'unexpected' validation rule failure you can trap the constraint name (or custom validation text, etc) to provide a decent error message e.g. (pseudo code): Private Sub btnOK_Click() On Error Goto Err_Handle ' Do front end validation: rules tests here, show message and exit on failure ' Attempt to write data configure command here On Error Resume Next command.Execute If Err.Number 0 Then ' Handle SQL errors he If InStr(Err.Description, "salary_periods_overlapping") 0 Then failure message for overlapping periods here ElseIf InStr(Err.Description... ... Else handle unexpected SQL errors here End If End If On Error Goto Err_Handle clean up here Exit Sub Err_Handle: catch VBA errors here End Sub And if you'd do entity type checking in the front end, why not do your unique indexes and FKs in the front end too?! Because there are no tables in the front end. In the example, the SUVs table is not in the front end, yet you are proposing make the test for vehicle_type='SUV' in the front end. Some things are not so convenient to test in the front end and are a lot easier to test with a database round trip e.g. duplicate entity testing: easy to trap a unique key failure message but more work to cache the entire table in memory to test for duplicates locally. Even if testing for duplicates locally you wouldn't omit the unique constraint from the database, so why omit the test vehicle_type='SUV' from the SUVs table? You know, I have actually tried to agree with you on some points. Indeed. Did you want me to acknowledge that we're agree (or otherwise) on certain points? I tend to concentrate efforts on the *unresolved* points. Jamie. -- |
#49
|
|||
|
|||
How do you mulitply in a field?
On 23 Aug, 06:06, "Brent Spaulding \(datAdrenaline\)"
wrote: I am surprized you do not agree. A VIEW is a 'table' by SQL-92 definitions, and a 'table' is 'a multiset of rows' by SQL-92 definition, and 'a multiset of rows' is a 'resultset' ... admittedly the definition of a 'resultset' is my interpretation based on context through out the thread. With that line of thought, a VIEW is, by definition, a 'resultset', which does not need to be normalized Put another way, SQL has but one data structure, being the 'table'. Yes ,you are correct here but I still need a term to differentiate between the 'table' that a stored proc spits out and the 'table' that a VIEW spits out because they *are* different even in SQL terms e.g. you can do this: SELECT ... FROM MyBaseTable AS T1 INNER JOIN MyView AS T2 ON ... and INSERT INTO MyView (...) VALUES ... but you can't do this: SELECT ... FROM MyBaseTable AS T1 INNER JOIN MyStoredProc AS T2 ON ... nor INSERT INTO MyStoredProc (...) VALUES ... Can you suggest a term to differentiate a SQL table that can be used in JOINs, can be INSERTed to etc? i.e. a collective term for base tables and VIEWs? I tried 'virtual table' but I can see no basis for this in the SQL-92 spec. This difference in tables type is one of the criteria I apply (Ill founded or no) when deciding whether a 'resultset table' is implemented as a stored proc or a VIEW i.e. if the VIEW was a base table for which someone could point to and say, "Your table is denormalized" then I'd make it a stored proc. If you do not agree to that, then, how 'bout this ... A VIEW is, by SQL-92 definition is a 'derived table' ... and 'derived tables' should not be forced to follow normalizton guidlines. I admit this sounds an interesting line of argument but on reflection I cannot think of an example where a 'denormalized' structure would be necessary in a derived table. Can you post a relevant example for consideration? TIA. "good discussion!" .. with respect of course! Agreed Jamie. -- |
#50
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message oups.com... On 22 Aug, 14:00, "BruceM" wrote: I do not know about clustering behavior See: New Features in Microsoft Jet Version 3.0 http://support.microsoft.com/kb/137039 "New compacting method. Compacting the database now results in the indices being stored in a clustered-index format. While the clustered index isn't maintained until the next compact, performance is still improved... The new clustered-key compact method is based on the primary key of the table." Defragment and Compact Database to Improve Performance http://support.microsoft.com/kb/288631 "If a primary key exists in the table, compacting restores table records into their primary key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient." I know that clustering exists. I just don't know what it means. I can't make an informed decision about the air pressure in my car's tires, either, because I don't understand the physics involved. However, people who know about such things have suggested the correct air pressure. I don't give it a lot of thought beyond that. Here's the bare bones: you've have this thing (PRIMARY KEY) which fulfils two main functions (null-free uniqueness and clustering respectively) where one function sometimes compromises the other (a sole unique column makes a poor cluster; multiple columns makes a clustered compound index look like a verbose key) but you are also given a second thing (NOT NULL UNIQUE) which performs one of the functions (null-free uniqueness), which leaves the other function (clustering) exclusive to the first thing (PRIMARY KEY). I can't think of anything car tire/tyre pressure related that fits the above so try this: Onedaywhen Cottage has central heating with zones, each zone has a portable digita thermostat with integral clock. Mrs Onedaywhen likes to keep the bedroom zone's thermostat on her bedside table/nightstand because it makes a handy bedside clock. I know enough to deduce she's chosen a lousy position for the thermostat e.g. the heat from the bedside light sometimes causes the heating to switch off prematurely. "Here," I say, "If you want to tell the time, use this alarm clock. Then we can put the thermostat in a more appropriate location." But we're at an impasse because people who know about such things have suggested to her that the clock's timekeeping is accurate by design because the boiler needs to know when to come on and she does not understand the physics of printed circuit boards and oil- fired boilers. But I keep telling her, "It's not just a clock, it's a thermostat too. Use something else to tell the time." She replies, "I do not know about thermostats." I've now given her some links to articles on the manufacturer's website so the ball's in her court. I can understand that heat (or lack of heat) causes a thermostat to switch on (or off), and that something affecting the conditions in the thermostat's immediate locale can cause the thermostat to perform other than as expected. This is because I understand the purpose of a thermostat and the factors that cause it to do its thermostat thing. When it comes to clustered indexes I have no such frame of reference. You mention physical order on the disk or something like that. I know enough about disks to know that contiguous portions of the disk from the computer's point of view (I can't think how else to put it) are not necessarily physically next to each other (at least that's how it was a few years ago). This has something to do with the disk's rotation speed and the fact that the read/write head can read something on one side of the disk, and then a third of a rotation later read the next thing, and so forth. However, if the next bit of information is exactly next to the first one it's too soon for the read (the hardware can't keep up), so the disk needs to rotate a full revolution and a bit more. Knowledgeable people have pointed out that there are many performance factors to be considered before physical ordering on the disk, and have questioned whether such physical ordering is really relevant, at least in an office-sized database. While I accept the validity of a multi-field index (or several single-field indices), I don't accept the physical ordering argument. Again, I apply security so that the database cannot be changed outside of the parameters I choose as the developer. The security can be circumvented by a determined and knowledgeable user, but for my situation it is adequate. It never occurred to me that somebody would take the Excel or some such similar route, which sounds to me like a skilled and determined attempt to undermine the data integrity. If the user can get through the security, no approach to indexing is going to help. The answer, I'm afraid, is that I don't see your point. Again, I'm not referring to malicious attacks. If you give people permissions on database object then in my experience sooner or later someone will think it's a good idea to access those objects via Excel e.g. to help them do their job better. This doesn't give them the right to act irresponsibly, though. Perhaps they thought it would be OK to correct some data directly in the table and are unaware you have some data integrity correction code in one of your forms that has now been inadvertently circumvented. If they have the permissions of which you speak they are able to add incorrect data that fulfill the table-level constraints. If anybody with a little bit of skill can get into a secured database by way of Excel than I am a poor developer in that I am utterly incapable of safeguarding the data. Even if their intentions are the best, they should not be able to get into the tables directly. If I've been given a key to the stationary cupboard and I need to sign some paper copy then I'll help myself to a pen. I still know that having a key doesn't mean it's OK for me to go home today with ten pens in my pocket. If you don't want me to have one of the pens, take away my key; if you want to allocate pens yourself, keep them locked in your desk drawer so that I *must* go through you to get a pen. It's pretty clear that picking locks is illicit behaviour. But if I've used my key to acquire a pen for my work, I don't see myself as having successfully made a skilled and determined attempt to undermine your stock control system -- I'm just trying to do my job. Perhaps there is some middle ground e.g. you provide communal pens in handy locations *but* keep them attached to chains, like they do in banks, so that I don't do my usual trick of sticking it behind my ear then forgetting and walking off with it. Providing a SQL PROC or VIEW in the SQL DBMS is the provision of pens (i.e. facilities me in doing my work without too much inconvenience), and integrity constraints and data validation rules at the data engine level are the chain (i.e. stops me messing up the system for my own sake and others). Saying I have to always use your application's forms is like saying I have to always use your pen, IMO too restrictive: what if your pen has the wrong color/colour ink, doesn't work as well as I like, you're not in the office today etc. And before you got upset that I found a pen myself, had you mentioned that you are considered to 'own' all the pens? If you did mention it, why do I have a key to the pens cupboard? Integrity constraints are one thing, but I don't know that my time would be well spent devising what amount to backup validation rules at the table level. If my front end does not stop data that violate data integrity rules that are enforced at the table level I can accept the default error message. The users will let me know. Again, if you've found a way of granting privileges to the application rather than to users (the latter being the most commonly seen scenario) then please share your techniques, there is an Access MVP in another thread wanting such information. Actually, I grant permissions to groups, which is the most common scenario, but is in the same category as granting permissions to individual users. I don't know what you mean by "granting privileges to the application", but I don't think I ever claimed I could or argued in favor of finding such an approach. I want the user to be informed by words of my choosing rather than default error messages. I want to intercept the problem before then. The front end is for the user's convenience, and I want it to be as convenient as is possible. Me too. Why not trap failures in the front end and in the data engine? i.e. attempt to trap validation rules violations in the front end but also have a validation rules in the data engine as a last line of defence against things the front end guy missed, VBA code bugs, etc. Errors generated by the engine can be caught so even when the front end does encounter an 'unexpected' validation rule failure you can trap the constraint name (or custom validation text, etc) to provide a decent error message e.g. (pseudo code): Private Sub btnOK_Click() On Error Goto Err_Handle ' Do front end validation: rules tests here, show message and exit on failure ' Attempt to write data configure command here On Error Resume Next command.Execute If Err.Number 0 Then ' Handle SQL errors he If InStr(Err.Description, "salary_periods_overlapping") 0 Then failure message for overlapping periods here ElseIf InStr(Err.Description... ... Else handle unexpected SQL errors here End If End If On Error Goto Err_Handle clean up here Exit Sub Err_Handle: catch VBA errors here End Sub And if you'd do entity type checking in the front end, why not do your unique indexes and FKs in the front end too?! Because there are no tables in the front end. In the example, the SUVs table is not in the front end, yet you are proposing make the test for vehicle_type='SUV' in the front end. NO!! When they select SUV the combo box for models, which gets its row source from SQL, will be redefined or requeried as needed. If this fails then the user is presented with a list of all models or the wrong models or something. Engine-level constraints will not help the user, who may have no relevant choices. I get what you are saying about how if I fail as a developer and present the user with invalid choices, but the user makes one of those choices anyhow, then there is nothing at the table level to stop them. If the user selects Corolla as a type of SUV (because I have presented the user with an invalid list of choices), you are correct that my database design does not guard against that happening at the table level. It is something to think about, now that I finally understand that you are making a distinction between an index and a PK. Some things are not so convenient to test in the front end and are a lot easier to test with a database round trip e.g. duplicate entity testing: easy to trap a unique key failure message but more work to cache the entire table in memory to test for duplicates locally. Even if testing for duplicates locally you wouldn't omit the unique constraint from the database, so why omit the test vehicle_type='SUV' from the SUVs table? You know, I have actually tried to agree with you on some points. Indeed. Did you want me to acknowledge that we're agree (or otherwise) on certain points? I tend to concentrate efforts on the *unresolved* points. You have got me to thinking about some things that I really hadn't considered before. I have attempted to agree with you, only to have you fire back as if I had never said anything. That gets old in a hurry. As I have said, I use indexes on fields that I expect to be used for sorting, filtering, and so forth. I have not given much thought to multi-field indexes. For instance, if five fields are required I suppose I could combine them into a multi-field non-null index, but what if 20 fields are required? There are limitations to enforcing data integrity in this way. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|