Subject RE: [firebird-support] Indexes or Tables?
Author Alan McDonald
> Hi All,
> I have a stituation here. I am just posting this message because I want to
> understand and share with all other the performance issue regarding tables
> and indexes.
> I am developing an application where performance is the most important
> issue.
> I have one table called CO = "customer order"
> I have one table called CO_ITEM = "the items of the order CO"
> I have one table called INV = "invoice"
> I have one table called INV_ITEM = "the items of the invoice INV"
> Now happens that the difference between CO and INV is just some fields.
> Most fields are common for both tables.
> Now there is another fact: I need an historic with all changes with CO.
> 1) create a single table INV for both INVs and COs with all the fields.
> 2) create a single table INV_ITEM for both INV_ITEMs and CO_ITEMs.
> 3) for the historic question I just thought about adding four fields in
> the primary key of both tables (INV and INV_ITEM) .
> The three fields are:
> a) a date field for the historic : dt
> b) a user id : user_id
> c) a field to tell me if the record is a INV or a CO
> d) a field to tell me if the record is the present situation
> I would like to be sure if this is the best approach or, for instance, I
> should keep more tables for better performance.
> Thanks in advance,
> Marcelo Miorelli

Neither same nor separate tables will reap you any significant performance.
FB will handle both cases with aplumb. Your choise totally. What's easier to
manage fro your point of view? I suspect separate tables are far easier to