Re: Table Design for 2 similar table

Ginger Mall (bocgco_at_hongkong.com) wrote:
: I am facing a question on schema design.

: There is 2 types of records. The 1st is start record of some activities
: while the other is the stop record.
: They contains actually different attributes except "UserID" and "DateTime".
: The query will only extract the result by UserID and sort by DateTime.

: e.g.
: .. User111 Start .......
: .. User111 Stop .......
: .. User111 Start .......
: .. User111 Stop.......
: .. User111 Start .......
: .. User111 Start .......
: .. User111 Start .......
: .. User111 Stop .......
: .. User111 Start .......

: My problem is should I split these records into 2 diff. table ?

If you're thinking about the table layout then why not split them into three tables?

	table1 - events
	user  timestamp event_type event_details_key	

	table2 - start_event_details
	event_details_key  various details for this start event

	table3 - stop_event_details
	event_details_key  the details applicable to stop events

(perhaps the user should be in the event details)

• = =

I would map out the queries I anticipate needing (all queries, insert, select, delete, etc) and get a feel for what makes the application easiest to write.

I would map out the size of the database (assuming there's enough data for that to potentially be an issue) and estimate what difference the table layout makes.

The space requirement may already force one layout over another in which case that's your layout. Otherwise, I would choose the layout that is easiest to use in your application.

Pay more attention to the ease of adding/modifying/deleteing data. For selecting data you can always define views and indexes to make the tables look like any layout you wish, but views are less capable for when the data is being changed.

$0.02 Received on Wed Dec 18 2002 - 14:29:13 CST