Best option: single tables or separate tables

[bluearrow]

Hi,

I have a database that includes the following:

1 Project table

1 Line1 table

1 Line2 table

The relationships are as follows:

- Every project has at least one Line1 and one Line2 items, but may have many more than one.

- Any given Line1 item can only be part of one project (only one project ID).

- Any given Line2 item can only be part of one project (only one project ID).

- Line1 items and Line2 items are, therefore, related among themselves by the project ID.

- Line1 items are related to Line2 items as a many to many relationship.

- The total cost of an specific project is roughly Total Line1 cost (what the client pays for the service) / Total Line2 cost (what we pay to our workers).

- The cost of an specific line is roughly Line1 cost / Line2 cost (not nearly the same as the cost of a project, for many reasons).

- Line1 items constitute the Invoice line items.

- Line2 items do not take part on the invoice (we receive invoices for them from our freelance workers).

- Line1 and Line2 items mostly share the same fields (more than 95%).

- In a year, there could be more than 50.000 line items in total.

The cost calculation is really more complex and the whole picture is much more complicated, but this is the basic idea. A lot of reporting will be done based on these 2 different line items.

Based on the above, what is the best option:

- Keep Line1 and Line2 Items on separate tables

- Make a single table for both line items.

My doubt come from:

- the (unknown) future problem of running reports and statistical calculations based on information coming from 2 different tables. A lot of reporting will be done.

- trying to related Line1 items and Line2 items inside the same table could be an issue (?). Maybe is easier if they belong to different tables.

- On the other hand, if they share most of the fields, having them in one single file might simplify the whole database and make future changes an easier task (?).

Thanks in advance

[David Jondreau]

I think you already know the answer.

One table for Line Items.

Your one objection seems to be relating the items correctly, but that's a pretty small hurdle.

Identify each line item as being of one of the two types. Create two calc fields in each of the Line Item and Project tables. Whenever you need to specify one type in a relationship, make sure you put an extra criterion in the relationship of the Line Item Type. This includes a self join.

[bluearrow]

Hi,

Please take a look at the attached file. Basically, it is a Client, Contact and Addresses database. The tables have been created and the links established, using several join tables.

I need to accomplish the following:

- A client may have many contacts.

- A client may have many addresses.

- A contact may have many addresses.

- All contact addresses come from existing client addresses (no contact may have an address that has not been previously created and associated with a client). This implies that the user must be able to select from a pool of addresses belonging to the client the contact belongs to.

- Contacts may share the same address as other contact (many contacts may be associated with the same address as other contacts).

- Client may share the same address with other clients (many clients may be associated with the same address).

Could you let me know if the TOG groups I have created are correct according to what I have stated?

Would it work?

Is there a better/simpler/more reasonable way to accomplish the same?

Thanks in advance

CL_CONT_Address.zip