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:252892 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
