Re: Table Design for 2 similar table

"Ginger Mall" <bocgco_at_hongkong.com> writes:

> Yes, you understand my problem 100%.
> If I combine them into 1 table, it will be easier to be extracted.
> Otherwise, I have to union the 2 tables in order to get the result order by
> datetime. however, as the 2 table have diff. attributes, I am not sure if
> there are any simple way to make them union together.

Well, you would need to have some common attribute between the two tables in order to join them together - something to link the start and stop records. Either this would be used to link two records from two different tables or it will need to be used to link the records in order to make the record in a one table solution.

When you say they have conflicting attributes, do you mean they have the same attributes with different values or are they similarly named attributes, but which actually represent different "real" attributes e.g. start/stop attributes with the same attribute name?

It may not be a problem as you could just label some of the attributes start attributes and some as stop attributes. The main question should still probably be to look closely at what you want to model - is it the start and stop records as individual entities you are primarily interested in or is it the "transaction/even" represented by these records? If the former, I'd lean towards a two table implementation and if the later, 1 table.

If you have lots of events/transactions which only have a stop record or a start record and not both and if each have quite a few attributes, I might lean towards a two table solution rather than having a 1 table solution where many records would have half their columns filled with nulls etc.

-- 
Tim X.
tcross (at) northnet com au