Thank you all for the reply. I think I will go with the normalized approach. One master table and then a separate table for each type of event.
Regards Victor On 9/12/06, Tom Kitta <[EMAIL PROTECTED]> wrote: > > 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:252976 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
