Just don't forget that its faster to query DB directly then use a View (not by much) - rather then creating a view invest your time into a stored procedure(s). Should you use Stored Proc instead of straight SQL - well if you do a lot of querying most likely yes, if not then you may go with ad hoc SQL.
-----Original Message----- From: Matt Williams [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 12, 2006 5:54 PM To: CF-Talk Subject: Re: SOT Database design Knowing that future event types may be added, I vote for option 1. It feels wrong to have have a bunch of sometimes unnecessary fields. If you're on MSSQL (probably doable in other DBs, but I wouldn't know), you can set up 'views' which look like a table to CF, but do the join you are talking about. On 9/12/06, Tom Kitta <[EMAIL PROTECTED]> wrote: > With the example you describe you can get away with a single table unless > you predict changes in the future or application revolves around these > events etc. Example: ColdFusion error logger - logs different > ColdFusion errors. You can just have one error table and record all possible > error properties that CF can throw even through non-SQL error will not have > SQL info in them. I use single table and don't even bother recording most > events in separate fields. > > On the other hand more database normalized approach would be to have more > than one table - for example event master table, linked to event type and > say event line item table with event line item type table. 4 tables - you > can model all different event types now - normalized ... but does you > application need this? > > In short if the events are important to the application, its central part, > do the normalized approach. If they are just a side thing and your > application development budget is not through the roof, you can settle down > for one table approach (if the events are not very disjoint - otherwise you > have no choice). > > TK > > -----Original Message----- > From: Victor Moore [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 12, 2006 4:14 PM > To: CF-Talk > Subject: SOT Database design > > > Hi, > > I have a database design question. I have to model a db to capture multiple > types of events. While all of them are events the information needed to be > captured for each subcategory of event is quite different. > I think there are (at least) two ways of doing it: > 1. Have a master event table that captures all the information that is > common to all events and then have other tables that capture information > that is specifically to each category linked to the master table. > 2. Use just one table to capture all the information needed for all types of > events. > > Not sure which one will be the best approach from a point of view of speed > and maintenance. > In the first case I will need a joint between the tables to retrieve all the > info about an event, but has the advantage that if a new event type is > required I can add it without modifying the existing code. > In the second case no joint, so easier retrieval but maybe a little bit more > involved if I need to expend it. > > Anyone has any suggestions? > > Thanks > Victor > > PS Platform will be CFMX 7.x, MS-SQL 2005 and I estimate about 5k events > per year with about 45-50 columns if using just one table > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252917 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
