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 |
#11
|
|||
|
|||
How do you mulitply in a field?
On Aug 16, 3:19 pm, "BruceM" wrote:
If you don't have a problem with storing a calculation in a base table, then what is the normalization issue when it comes to queries? I doubt you are arguing against normalization in base tables, but where are you trying to go with this? How would you go about performing calculations? If the results are stored in a table, something can be done to force recalculation as needed, but what does that gain you? It's true, I'm not very strict about normalization in base tables, ditto VIEWs. Quite strict, though, because I require a good reason and assurance of data integrity. But equally strict because both are tables. Usually I have no good reason, consequently I make it a stored proc. I think those who are very strict about normalization in base tables should be very strict about normalization in virtual tables. But you know me: I think every table (including VIEWs) should have an enterprise key defined by business rules; if the only unique constraint is defined on a sole autonumber column then I wouldn't consider the object to be a table! Jamie. -- |
#12
|
|||
|
|||
How do you mulitply in a field?
OK, that clarifies what you were saying.
Regarding autonumber or other arbitrary PKs: If I have a listing of vendors, what is the *unchanging* unique constraint? It is not name or address in any combination, which are subject to change (thereby meaning related records would also need to change). Maybe it could be the tax ID number, but then you would need that information first, which is not always practical. Or maybe it would be an arbitrary number in combination with other fields that are not subject to change, such as...hmm. Or maybe cascading updates of multi-field keys are not a problem. I would rather avoid them, especially if another database links to that table. You will not change my mind on the subject, nor I yours. But understand that I *never* argued in favor or creating a table for which the "only unique constraint is defined on a sole autonumber column". A record's uniqueness is one thing. The means of identifying it in relationships is another. The two may be the same, or they may not. I'm not going to bind myself to an absolute rule based on somebody's design theory. Rather, I will make the necessary choices as I go. "Jamie Collins" wrote in message ups.com... On Aug 16, 3:19 pm, "BruceM" wrote: If you don't have a problem with storing a calculation in a base table, then what is the normalization issue when it comes to queries? I doubt you are arguing against normalization in base tables, but where are you trying to go with this? How would you go about performing calculations? If the results are stored in a table, something can be done to force recalculation as needed, but what does that gain you? It's true, I'm not very strict about normalization in base tables, ditto VIEWs. Quite strict, though, because I require a good reason and assurance of data integrity. But equally strict because both are tables. Usually I have no good reason, consequently I make it a stored proc. I think those who are very strict about normalization in base tables should be very strict about normalization in virtual tables. But you know me: I think every table (including VIEWs) should have an enterprise key defined by business rules; if the only unique constraint is defined on a sole autonumber column then I wouldn't consider the object to be a table! Jamie. -- |
#13
|
|||
|
|||
How do you mulitply in a field?
Again, this is your assertion, but I'm looking for the basis.
Regards Jeff Boyce Microsoft Office/Access MVP "Jamie Collins" wrote in message ps.com... On 16 Aug, 13:04, "Jeff Boyce" - DISCARD_HYPHEN_TO_END wrote: I'm curious why the same admonition about rarely needing to store calculated values in a table would have to be applied to views, i.e., your "virtual tables"? Do you have a reference so I can read up more on this? I would have no idea where to look for such a reference. Normalization applies to logical tables. The distinction between base tables and virtual tables is a SQL (the language) concept. You can create a SQL table with no key; likewise, you can create a SQL table that is denormalized. But if you buy in to the idea of aiming for the highest possible normal form -- and I suggest that you should -- then why would you not apply it virtual tables? Should a VIEW have a key? I'd say so. I'm probably not the best person to defend a normalization stance because personally I don't have too much of a problem with calculated columns in a base table a) if there's a good reason for doing so and b) when integrity constraints are present to prevent values going 'out of sync'. A phrase that comes to mind from long past studies for taxation exams: "Strict rules modified in practice." Jamie. -- |
#14
|
|||
|
|||
How do you mulitply in a field?
Although your post produced an interesting discussion, I'm guessing you were
looking for an answer. I'm not an Access MVP or anything, just a regular guy, but I would agree with fredg that you should not try to store calculations in a table. Tables are for storing raw data. You use queries to manipulate the data and perform calculations. I doubt the survival of the free world depends upon your database, so if your queries aren't technically "normalized", I think you'll be OK. "wazabbbi" wrote: I'm a newbee at this whole access thing. The purpose of this database is to calculate fees and total fees collected for a project. I wanted to be able to store the result in the same table. I was able to have the result calculated on the form but the version I have of access is so old that I can only run reports from a table. To further complicate things I have another field that I want to have the total of the inspection fee and two other fields displayed and stored. In excel its simple enough to do but I'm having trouble figuring out how to do it in access. Eventually I'd like to run reports showing total fees collected for a set period of time but also for a particular applicant (our applicants may have multiple projects). Can you guys please elaborate as to the best way for me to accomplish my goal. Thanks =) "Jamie Collins" wrote: On Aug 16, 2:16 pm, "Roger Carlson" wrote: I don't see why this should be contradictory. The fact is the calculated column is not "stored" in a Query, so the cases are not even similar. Since when did normalization have anything to do with physical storage?! Consider that in some SQLs (e.g. Oracle) VIEWs can be materialized i.e. their data *are* physically stored. I repeat: normalization applies to logical tables. By the same reasoning, we should never store a Join of two tables because that would produce data redundancy. A JOIN in a query does not by necessarily result in a denormalized structu you have to consider the whole: SELECT clause, WHERE clause, GROUP BY clause, etc. But yes, the reasoning is the same: reundant data in a VIEW, regardless of how it was derived, consistutes denormalization. Jamie. -- |
#15
|
|||
|
|||
How do you mulitply in a field?
I'm a newbee at this whole access thing. The purpose of this database is to
calculate fees and total fees collected for a project. I wanted to be able to store the result in the same table. I was able to have the result calculated on the form but the version I have of access is so old that I can only run reports from a table. To further complicate things I have another field that I want to have the total of the inspection fee and two other fields displayed and stored. In excel its simple enough to do but I'm having trouble figuring out how to do it in access. Eventually I'd like to run reports showing total fees collected for a set period of time but also for a particular applicant (our applicants may have multiple projects). Can you guys please elaborate as to the best way for me to accomplish my goal. Thanks =) "Jamie Collins" wrote: On Aug 16, 2:16 pm, "Roger Carlson" wrote: I don't see why this should be contradictory. The fact is the calculated column is not "stored" in a Query, so the cases are not even similar. Since when did normalization have anything to do with physical storage?! Consider that in some SQLs (e.g. Oracle) VIEWs can be materialized i.e. their data *are* physically stored. I repeat: normalization applies to logical tables. By the same reasoning, we should never store a Join of two tables because that would produce data redundancy. A JOIN in a query does not by necessarily result in a denormalized structu you have to consider the whole: SELECT clause, WHERE clause, GROUP BY clause, etc. But yes, the reasoning is the same: reundant data in a VIEW, regardless of how it was derived, consistutes denormalization. Jamie. -- |
#16
|
|||
|
|||
How do you mulitply in a field?
Normalization is the level of efficiency your data model stores data
that is INPUT. A View is an OUTPUT of the normalized data and has no buisness being part of the data storage model ... even if the data that is OUTPUT to the View is stored in your db (ie a Make Table query in Access), that block of data is NOT part of the data storage model for your application. If your datamodel is dependant upon on OUTPUT (ie a View or Make-Table query), then I would stick my neck out and claim that you are not normal. Regards, Brent Spaulding | datAdrenaline | Access MVP |
#17
|
|||
|
|||
How do you mulitply in a field?
On Thu, 16 Aug 2007 18:21:29 -0700, wazabbbi
wrote: the version I have of access is so old that I can only run reports from a table. Ummm.... Access 2.0, back 15 years ago, let you base reports on queries. And I'm pretty certain that 1.0 and 1.1 did also. John W. Vinson [MVP] |
#18
|
|||
|
|||
How do you mulitply in a field?
On 16 Aug, 19:53, "Jeff Boyce" wrote:
I'm looking for the basis. What, that a VIEW is a logical table? Have you tried doing a google search? e.g. sql view virtual OR logical table http://www.google.co.uk/search?q=sql...+logical+table Now try the same using e.g. normalization view virtual OR logical table http://www.google.co.uk/search?q=nor...ogical+tab le though you could drop the word 'virtual' from the search because 'virtual tables' are a SQL (the language) concept. From these readings you may glean the following formal process: 1) As Mrs Beeton would say, "First, catch you your business model." 2) Design a logical model applying the rules of normalization to create logical tables and the relationships between them. 3) Implement your logical model in SQL (or other relational technology); the logical tables from your logical model sometimes are directly implemented as a physical SQL base table and sometimes not. So, in my book, my SQL implementation must be true to my logical model and in turn my logical model must be true to normalization. If you are looking for a reference that applies normalization to VIEWs then I don't think you will find one because there is a stage in between -- logical model -- that cannot be disregarded. I don't see anything in this process that offers 'normalization immunity' to virtual tables. Jamie. -- |
#19
|
|||
|
|||
How do you mulitply in a field?
On 17 Aug, 05:26, wrote:
Normalization is the level of efficiency your data model stores data that is INPUT. A View is an OUTPUT of the normalized data and has no buisness being part of the data storage model ... even if the data that is OUTPUT to the View is stored in your db Normalization only applies to INPUT? Base tables = INPUT, virtual tables = OUTPUT? INSERT INTO MyView... UPDATE MyView SET ... DELETE FROM MyView WHERE... Where did you get such notions?! If your datamodel is dependant upon on OUTPUT My physical implementation in SQL is dependent on my logical model, not the other way around. My logical model does not directly consider INPUT and OUTPUT, those are for the font end. My SQL implementation sometimes relies on VIEWs. Example: one application requires a time-valid state (history table with periods) and a second application is only interested in the current state, therefore I create a VIEW (...WHERE end_date IS NULL, 'hide' the base table while 'exposing' the VIEW via privileges, use INSTEAD OF triggers and WITH CHECK OPTION to manage updates to the VIEW. Though the users of the second application may discover it's a VIEW rather than a base table they should not care because logically a table is a table is a table. (ie a Make Table query in Access), that block of data is NOT part of the data storage model for your application. I don't get your 'Make Table query' example. IMO no application should be creating permanent tables on the fly. Because though Access/Jet SQL does not support the CREATE TEMPORARY TABLE or similar syntax (unless the Help is to believed and I suggest it isn't: http://office.microsoft.com/en-gb/ac...314411033.aspx) I can see the temptation, however I'm told that dropping an re-creating tables causes file bloat. I have tables in my SQL implementation that are not part of my logical model. Example: permanent load tables I can populate, operate on using SQL to populate other tables then clear down; the most frequent use it to normalize 'non-relational' data (e.g. csv) therefore such tables are denormalized out of necessity. I say that tables that are part of the logical mode should be normalized (unless there's a good reason to denormalize, always ensuring data integrity). I have permanent auxiliary tables that are denormalized. Example: Calendar table with columns for date (instant), year, month, day, week, is_weekday, etc. Theses tables are to help me and the users write simply, easy to maintain queries. There's always costs when denormalizing but here the benefits outweigh them. I have permanent base tables that are denormalized to work around a physical limitations in the SQL DBMS. Example: I must handle a real life enterprise key, for which the trusted source is a government agency, comprising one thousand ASCII characters (the civil servant who designed it has since offered me a personal apology g), the SQL DBMS's I use employs indexes to implement unique constraints where there's a limit on the number of bytes an index value can comprise, so I have to store a hash of the real key value so that my integrity constraint (CHECK, trigger, etc) used to ensure uniqueness can use the non-unique index on the hash for performance reasons. The hash does not exist in the logical model so I figure I haven't denormalized that. In the physical implementation I need the performance *and* I need the uniqueness and all is fine by me because the hash is constrained to be a true hash of the actual value i.e. can't do out of sync. You can consider my table to denormalized but my conscience is clear g. I would stick my neck out and claim that you are not normal. In my country (UK), it is the custom to ROFL when a devastatingly good pun is made. Jamie. -- |
#20
|
|||
|
|||
How do you mulitply in a field?
Hi Jamie,
Technically, normalisation applies only to the logical relation, not to a physical table or any view of it. As I would hope you know, any visualisation of the data conained within a table, is a view; and it's implemented as a view behind the scenes. Views are not virtual tables, they are visualisations of datasets. If you open a "table" to look at its data, you're visualising that data through an additional layer called a view, not the table itself, and certainly not the relation. You can never "see" a table's data directly, because a table is just the physical implementation of a structured storage mechanism. Whether I choose to view a slice of my data horizontally, vertically, or include joins or calculated fields, the rules of normalisation applied to the relation(s) remain(s) unchanged, because I have not changed the relation(s). But to create a calculated field in a relation would indeed constitute a breach of normalisation rules. Strictly speaking, materialised views can be normalised or un-normalised, and that's OK, because they are not the primary data storage relations. They are simply views that have been materialised for purposes other than data storage. They're equivalent to temp tables, so the rules don't apply. Just as an aside; in a later post you state that we should always aim for the highest normal form. That's not entirely accurate or desirable. I'm sure that when you think about it, you'll agree. DKNF or (heaven forbit) Lossless Joins are not for the feint of heart; nor are they anywhere near practical in 99.99% of cases. Even in data warehouses, we rarely go beyond 4NF. You're correct when you quote, "Strict rules modified in practice". A classic example is financial data models, where storing current balances in tables are the norm. The relations are designed that way, thus lowering normal form. In any case, you have to make the distinction between (a) the storage of data, and (b) the use of that data. Pure storage requires normalisation. Use of the data requires whatever form is necessary to convert that data into information or knowlege. Therefore, denormalising views by including calculated fields is OK, because it is an output function, not a data storage function. Data modelling 101. I think those who are very strict about normalization in base tables should be very strict about normalization in virtual tables. No. One is not the other. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia "Jamie Collins" wrote in message oups.com... On 15 Aug, 22:05, fredg wrote: I created a table with two fields named "projectestimate" and "inspectionfee". The inspection fee is 5% of the project estimate. I want the "inspectionfee" field to automically calculate the amount. How do I do that? In a table? You don't. Access tables are for storing data, not for data manipulation or display. you can do the calculation directly on a form or report, using an unbound text control: = [ProjectEstimate] * [InspectionRate] the resulting value should not be saved in any table. In this simple case I agree: there can be no good reason for storing the result. If you store the [ProjectEstimate] and [InspectionRate] it's easy enough to calculate the actual fee in a Query: InspectionFee:[ProjectEstimate] * [InspectionRate] Not sure I agree anymore. Did you mean to say 'query' (lowercase)? If you saved your query with the calculated column as a stored Query (Title Case) object then this would become a virtual table. You would seem to be saying that denormalization in a base table is wrong but fine in a virtual table. That sounds contradictory. For me, this is one of the differences between a VIEW and a PROCEDURE i.e. the resultset of a PROC doesn't need to be normalized. The trouble is, how do you get the Access/Jet engine to see your PROC as a PROC e.g. this in Northwind (ANSI-92 Query Mode SQL syntax): CREATE PROCEDURE GetOrderProcessIntervals AS SELECT OrderID, OrderDate, ShippedDate, DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days FROM Orders; actually creates a VIEW! i.e. although it can be EXECUTED: EXECUTE GetOrderProcessIntervals; it can also be queried as a table: SELECT * FROM GetOrderProcessIntervals; and we now have a denormalized table. The only workaround I can think of is to supply a useless parameter value e.g. CREATE PROCEDURE GetOrderProcessIntervals ( this_not_used INTEGER = 0 ) AS SELECT OrderID, OrderDate, ShippedDate, DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days FROM Orders; Now SELECT * FROM GetOrderProcessIntervals; fails with a 'missing parameter' error. So the choice seems to be to ask users to ignore daft parameter or be a little less strict on normalization rules. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|