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 |
#51
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
The DBAs should be intimate enough with the
schema to know where to look without hints g. That applies very well to the person who designed the database, but what about the DBA who simply has someone else's work dumped into his lap? Rob |
#52
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
But the apartment number will be very helpful.
All you have to do is stand in the middle of the room and look around. Unless they've left the apartment, of course, which sorta goes back to the point of converting a table to a view for whatever reason. (Okay, so I'm arguing against my own points here, but I acknowledge that just about ANY naming convention system has flaws.) Rob |
#53
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Keep in mind that views don't ONLY represent entities (in fact, I almost
never use them to represent entities...tables should do that in their own right, not views). Sometimes a view is useful simply to sort data in a certain order; for end users to export data to Excel or Word (sticking within the context of MS products for the moment); sometimes it's easier to construct and maintain complex views by splitting them into several smaller ones, even though the smaller ones will never be used outside the context of the larger one; sometimes a view may be used entirely internally by the DBA to "view" only the relevant information from one or more tables, rather than going to the base tables themselves. Others argue that all these sorts of things should be table-valued functions or SPs, but whatever your choice in THAT argument, it works out the same in the end...you're using SOMETHING to fulfill all these various needs (unless you're a proponent of writing SQL code in your front-end, of course, but let's stick with n-tier development for now). For myself, I have just shy of 100 tables in my database, so it stands to reason that I would have even more views (close to 500). And I don't consider my data model to be HUGELY complex...certainly not the simplest thing in the world, but not the most complex, either. I can see how a data model with several hundred tables could easily end up with thousands of views for various purposes. Rob "onedaywhen" wrote in message oups.com... david epsom dot com dot au wrote: I have more than 4000 views. I am fairly intimate with the schema, but I still don't remember all of the names: I know MM_Audit and DP_Contract, but am I looking for SystemUsers, or Users? Wow, that's a staggering amount. I've read anecdotally that the rule of thumb for the maximum number of tables in a SQL database is about a hundred. The most complex data model I know of, that being developed for the UK Nation Health Service, had around 200 classes last time I checked. I'm puzzled how you could have a need for so many views/queries. You have several thousand distinct entity types? Many legacy apps to support? And you are not sure the sheer number is not a maintenance issue...? Jamie. -- |
#54
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Robert Morley wrote: I almost never use [views/queries] to represent entities...tables should do that in their own right Not necessarily. Here's a usage example where exposing an entity via a view/query is useful in Access. Say you wanted to maintain a 'date inserted' column for a table: CREATE TABLE MyTable ( key_col INTEGER NOT NULL UNIQUE, data_col INTEGER NOT NULL, effective_date DATETIME DEFAULT NOW() NOT NULL ); Obviously you don't want the effective_date column to be written to directly e.g. a user could accidentally (or otherwise) enter a far future date; let's also say this metadata need not be exposed either, so you wrap it in a view/query: CREATE VIEW MyFakedTable AS SELECT key_col, data_col FROM MyTable; Revoke all permissions from the table and grant them to the view/query. Users can now insert into the view/query and the default 'timestamp' gets automatically applied: INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1); Of course, you are more likely to want a 'last modified' date i.e. the effective_date column is additionally maintained for updates. For Access, a stored proc/parameterized query is required e.g. CREATE PROCEDURE SetMyTableData ( arg_key_col INTEGER, arg_data_col INTEGER ) AS UPDATE MyTable SET data_col = arg_data_col, effective_date = NOW() WHERE key_col = arg_key_col; Obviously you would now revoke update permissions from the view/query. Note that on other platforms there may be alternatives to procs e.g. in SQL Server updates can be controlled via an INSTEAD OF trigger on the view or, for such a simple example, a regular trigger on the table. This may make the system easier to use than one view/query for inserts and another proc/query for updates (I'm remain uncomfortable about referring to an SQL DML UPDATE *command* as a 'query' i.e. it does not return a resultset). Which leads me to a question: is it common practice in Access to differentiate between the 'view' flavour of Query (e.g. prefix of 'vw') and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost ubiquitous to use a single prefix (e.g. prefix of 'qry') to reflect the fact that both flavours are lumped together on the same tab in the Access GUI? Jamie. -- |
#55
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Robert Morley wrote: sometimes it's easier to construct and maintain complex views by splitting them into several smaller ones, even though the smaller ones will never be used outside the context of the larger one; Yeah, I took account of this kind of usage but still thought it a bit high e.g. using a view/query to do in Access what a CTE does in SQL Server 2005. It's legitimate of course but you should consider replacing the 'smaller ones' with derived tables. For example, here's something I posted recently: SELECT DT1.Nr, DT1.Name, DT1.Priority, ( SELECT COUNT(*) FROM ( SELECT H1.Nr, H1.Name, H1.Priority, IIF(H1.Priority = 'Y', H1.Nr * 0.5, H1.Nr + 0.00000000000000000001) AS new_rank FROM Housing AS H1 ) AS DT2 WHERE DT2.new_rank = DT1.new_rank ) AS new_Nr FROM ( SELECT H1.Nr, H1.Name, H1.Priority, IIF(H1.Priority = 'Y', H1.Nr * 0.5, H1.Nr + 0.00000000000000000001) AS new_rank FROM Housing AS H1 ) AS DT1; This looks cleaner using two views/queries: CREATE VIEW SmallerOne AS SELECT H1.Nr, H1.Name, H1.Priority, IIF(H1.Priority = 'Y', H1.Nr * 0.5, H1.Nr + 0.00000000000000000001) AS new_rank FROM Housing AS H1; SELECT S1.Nr, S1.Name, S1.Priority, ( SELECT COUNT(*) FROM SmallerOne AS S2 WHERE S2.new_rank = S1.new_rank ) AS new_Nr FROM SmallerOne AS S1; It could be the case that the two approaches are treated the same by the parser/optimizer. I'm hopeful that the parser is smart enough to work out my two derived tables are exactly the same but it could true that if the derived table must be 'calculated' twice the same will apply to using the same view twice. However, code maintenance is more important to me than second-guessing the optimizer g. You've got to decide which approach has fewer maintenance issues for you e.g. bloating the schema vs repeating code (which may not be obvious) within the same object. Dervied tables work for me: I just love the fact that with SQL a complex task can be a 'one line' solution vbg. Jamie. -- |
#56
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Alright, that's fair enough. I generally just ignore the metadata when I'm
writing the data layer, or make it friend-only or read-only or whatever it is I want, but doing it through views would have advantages (as you pointed out) as well. As for "common practice", I can't speak to that, as I've seen a wide variety of methods when it comes to things like action queries, so I'm not sure what's "common". I sometimes use a prefix for the action type if the action query is a stand-alone, otherwise I use some grouping name. For example, if there was a query to delete a test respondent, it might be named "delTestRespondent". If I had a whole group of action queries that I normally ran in sequence, I would generally group them based on function, so maybe something like "TestRespondentDel", "TestRespondentCreate", "TestRespondentInsertFakeData", etc. (Or I might shorten them all to just "trDel", "trCreate", etc...depending on how annoying the full spelling got to be grin.) Because of the fact that an SP in SQL Server can have multiple statements, I tend not to name based on "action type" when I'm designing for SQL Server. This has nothing to do with any concept of superiority or what have you, simply the fact that single-statement SPs are very rare for me in SQL, so the convention isn't really useful to me there. Thanks, Rob |
#57
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Yeah, I took account of this kind of usage but still thought it a bit
high e.g. using a view/query to do in Access what a CTE does in SQL Server 2005. It's legitimate of course but you should consider replacing the 'smaller ones' with derived tables. For example, here's something I posted recently: I haven't really familiarized myself with 2005 as yet. I remember reading something about CTE's, but don't remember exactly what they are. As for the use of derived tables, yeah, I use those as well when it's something relatively straight-forward (e.g., "SELECT * FROM SomeTable WHERE SomeSimpleCondition), but anything with large numbers of conditions, or joins, etc., GENERALLY gets shunted to a second view to make maintenance easier. It goes case-by-case, of course, and being human, I don't promise that what gets evaluated one day for a separate view wouldn't get evaluated as a derived table six months later. It could be the case that the two approaches are treated the same by the parser/optimizer. I'm hopeful that the parser is smart enough to work out my two derived tables are exactly the same but it could true that if the derived table must be 'calculated' twice the same will apply to using the same view twice. Yeah, ditto on the "I'm hopeful" part. I can see one possible difference, which is that the stats that SQL Server maintains may be more efficient with one method than the other, but nowhere near intimate enough with SQL Server to comment on whether that's actually the case. I just love the fact that with SQL a complex task can be a 'one line' solution vbg. Oh yeah, definitely! vbg back Rob |
#58
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Which leads me to a question: is it common practice in Access to
differentiate between the 'view' flavour of Query (e.g. prefix of 'vw') and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost :~) Relevant question. As I noted in my long-winded message, there are conflicting design demands for the access designer. If you have good eyesight, there is no reason to annotate 'append' and 'update' commands: they are already annotated by an icon on the 'query' tab. If you have average eyesight, 'proc' flavours are usefully annotated as suffixes. This allows the 'queries' to sort logically by noun or verb. 'Proc' flavours are not visible as source objects in query design view, so a suffix like 'append' or 'update' may be used: it doesn't matter how long it is, and it doesn't matter that names that long are not fully visible in the default QBE view. Prefixes are used primarily to locate objects, not to specify their structu If I have a table/view/command prefixed "tblBS", I know that it is on the t tab in the BS database. '-------- Complex Access queries are normally built up as a structure referencing other Access queries: joins are used instead of subqueries. This makes it very valuable to be able to easily locate the referenced objects. I note that on other platforms, it is more common to use subqueries where an Access designer would reference a separately stored query. On other platforms, using a subquery allows you to see all of the relevant SQL in one place. That is good. But Access designers have a QBE window which is generally more productive than SQL view, and in the QBE window subqueries are poorly displayed, and where possible, Access queries built using joins between related objects run better than Access queries built using sub-queries. So Access developers tend to use stored 'query' objects, and need to know where to find them. '------- (david) "onedaywhen" wrote in message ups.com... Robert Morley wrote: I almost never use [views/queries] to represent entities...tables should do that in their own right Not necessarily. Here's a usage example where exposing an entity via a view/query is useful in Access. Say you wanted to maintain a 'date inserted' column for a table: CREATE TABLE MyTable ( key_col INTEGER NOT NULL UNIQUE, data_col INTEGER NOT NULL, effective_date DATETIME DEFAULT NOW() NOT NULL ); Obviously you don't want the effective_date column to be written to directly e.g. a user could accidentally (or otherwise) enter a far future date; let's also say this metadata need not be exposed either, so you wrap it in a view/query: CREATE VIEW MyFakedTable AS SELECT key_col, data_col FROM MyTable; Revoke all permissions from the table and grant them to the view/query. Users can now insert into the view/query and the default 'timestamp' gets automatically applied: INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1); Of course, you are more likely to want a 'last modified' date i.e. the effective_date column is additionally maintained for updates. For Access, a stored proc/parameterized query is required e.g. CREATE PROCEDURE SetMyTableData ( arg_key_col INTEGER, arg_data_col INTEGER ) AS UPDATE MyTable SET data_col = arg_data_col, effective_date = NOW() WHERE key_col = arg_key_col; Obviously you would now revoke update permissions from the view/query. Note that on other platforms there may be alternatives to procs e.g. in SQL Server updates can be controlled via an INSTEAD OF trigger on the view or, for such a simple example, a regular trigger on the table. This may make the system easier to use than one view/query for inserts and another proc/query for updates (I'm remain uncomfortable about referring to an SQL DML UPDATE *command* as a 'query' i.e. it does not return a resultset). Which leads me to a question: is it common practice in Access to differentiate between the 'view' flavour of Query (e.g. prefix of 'vw') and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost ubiquitous to use a single prefix (e.g. prefix of 'qry') to reflect the fact that both flavours are lumped together on the same tab in the Access GUI? Jamie. -- |
#59
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
It would be nice if you could right click on a name and get to a properties
window. "david epsom dot com dot au" david@epsomdotcomdotau wrote in message ... Which leads me to a question: is it common practice in Access to differentiate between the 'view' flavour of Query (e.g. prefix of 'vw') and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost :~) Relevant question. As I noted in my long-winded message, there are conflicting design demands for the access designer. If you have good eyesight, there is no reason to annotate 'append' and 'update' commands: they are already annotated by an icon on the 'query' tab. If you have average eyesight, 'proc' flavours are usefully annotated as suffixes. This allows the 'queries' to sort logically by noun or verb. 'Proc' flavours are not visible as source objects in query design view, so a suffix like 'append' or 'update' may be used: it doesn't matter how long it is, and it doesn't matter that names that long are not fully visible in the default QBE view. Prefixes are used primarily to locate objects, not to specify their structu If I have a table/view/command prefixed "tblBS", I know that it is on the t tab in the BS database. '-------- Complex Access queries are normally built up as a structure referencing other Access queries: joins are used instead of subqueries. This makes it very valuable to be able to easily locate the referenced objects. I note that on other platforms, it is more common to use subqueries where an Access designer would reference a separately stored query. On other platforms, using a subquery allows you to see all of the relevant SQL in one place. That is good. But Access designers have a QBE window which is generally more productive than SQL view, and in the QBE window subqueries are poorly displayed, and where possible, Access queries built using joins between related objects run better than Access queries built using sub-queries. So Access developers tend to use stored 'query' objects, and need to know where to find them. '------- (david) "onedaywhen" wrote in message ups.com... Robert Morley wrote: I almost never use [views/queries] to represent entities...tables should do that in their own right Not necessarily. Here's a usage example where exposing an entity via a view/query is useful in Access. Say you wanted to maintain a 'date inserted' column for a table: CREATE TABLE MyTable ( key_col INTEGER NOT NULL UNIQUE, data_col INTEGER NOT NULL, effective_date DATETIME DEFAULT NOW() NOT NULL ); Obviously you don't want the effective_date column to be written to directly e.g. a user could accidentally (or otherwise) enter a far future date; let's also say this metadata need not be exposed either, so you wrap it in a view/query: CREATE VIEW MyFakedTable AS SELECT key_col, data_col FROM MyTable; Revoke all permissions from the table and grant them to the view/query. Users can now insert into the view/query and the default 'timestamp' gets automatically applied: INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1); Of course, you are more likely to want a 'last modified' date i.e. the effective_date column is additionally maintained for updates. For Access, a stored proc/parameterized query is required e.g. CREATE PROCEDURE SetMyTableData ( arg_key_col INTEGER, arg_data_col INTEGER ) AS UPDATE MyTable SET data_col = arg_data_col, effective_date = NOW() WHERE key_col = arg_key_col; Obviously you would now revoke update permissions from the view/query. Note that on other platforms there may be alternatives to procs e.g. in SQL Server updates can be controlled via an INSTEAD OF trigger on the view or, for such a simple example, a regular trigger on the table. This may make the system easier to use than one view/query for inserts and another proc/query for updates (I'm remain uncomfortable about referring to an SQL DML UPDATE *command* as a 'query' i.e. it does not return a resultset). Which leads me to a question: is it common practice in Access to differentiate between the 'view' flavour of Query (e.g. prefix of 'vw') and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost ubiquitous to use a single prefix (e.g. prefix of 'qry') to reflect the fact that both flavours are lumped together on the same tab in the Access GUI? Jamie. -- |
#60
|
|||
|
|||
Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
david epsom dot com dot au wrote: Access queries built using joins between related objects run better than Access queries built using sub-queries. I remember reading our very own Albert D. Kallal saying the opposite: http://discuss.fogcreek.com/joelonso...68&ixReplies=5 "since you are working a query on a query (that is View for you sql folks!), then often dumping the view, and placing the whole mess into one query can often help. You can wind up losing some indexing when you start stacking views on views" I guess the operative words here are 'often' g. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update combo box in subform (After Update event) | Karl | Using Forms | 10 | April 4th, 2006 07:45 PM |
Looking for a recent thread on multple combo boxes | potter | Using Forms | 7 | February 28th, 2006 03:31 AM |
Requery Combobox | MJ | Running & Setting Up Queries | 7 | May 25th, 2004 11:01 AM |