Not sure if db setup is right or wrong??

[alexankius]

Hi all,

I'm a "recreational" access user. The db i'm trying to setup should hold the following information:

1. Years
2. Clients (a client can have many contracts)
3. Contracts (a contract can have only one client. a contract can have many suppliers)
4. Suppliers (a supplier can have many contract)

With the above in mind i made the following tables:

1. tbl Year (year ID, year)
2. tbl Clients (client ID, year ID, Client Name)
3. tbl Contracts (contract ID, Client ID, contract name, fee,
4. tbl Joint (Joint ID, Contract ID, Supplier ID)
5. tbl Supplier (Supplier ID, Supplier Name)

relationships
1 to 2 (1 to many, "year ID" to "year ID")
2 to 3 (1 to many, "client ID" to "client ID")
3 to 4 (1 to many, "contract ID" to "contract ID")
5 to 4 (1 to many, "Supplier ID" to "Supplier ID")

Now, if I group by "Year", group by "Contracts" and Sum by "Fee", I get more fees than I should.

What is happening is that as "tbl Joint" can contain more than 1 record for each contract, the fee is multiplied for these records and therefore the total fee is more than it should.

The question is whether the database design is correct or not and therefore should i change the design, OR if i should change the query to select distinct in which case the result can be obtained.

What do you think?

thanks,
alex

 

[peregrin]

Hi alex,
I'm not sure what a joint is. Can you explain that? Otherwise, at first glance, I don't see why YearID is part of the clients table. Seems like (ordinarily) that's a property of a contract, not a client.

 

[spikepl]

I'm not sure what a joint is.

A Joint is something recreational users enjoy

In this particular case of Access, to maintain a many-to-many relation, like between the contracts and suppliers.

#1 Show the query. Sounds like you haven't joined the tables in the query.

 

[peregrin]

Okay, got it.

What is happening is that as "tbl Joint" can contain more than 1 record for each contract, the fee is multiplied for these records and therefore the total fee is more than it should.

I don't see why you'd need joint in this query (you can query for contracts and clients without worrying about contracts and suppliers).