Relationship Q: trying to find better way 1

[simon551]

This is a time and billing database. Something just feels wrong, but I can't put my finger on it.

I'm setting it up like this:

tblBillingCode
BillingCodeID
BillingCodeName


tblRetainers
ClientID
BillingCodeID

tblActuals
ClientID
BillingCodeID

tblActualTime
BillingCodeID
ClientID

The purpose of the tblRetainers column is to have a table where we hold "default" actuals for the tblActuals table.

Thanks in advance if you have any suggestion to straighten this out.

-s

 

[Remou]

Why have all these tables? I would have thought that two tables, Billing and Billing Details, would work. Billing details would hold the time sections, and the main Billing table would tie this together into a project unit. I would imagine that Actuals and Retainers exist for each project and only once, so both can be held in the main Billing table. This could be supported by look-up tables, such as Clients and Billing Codes, where necessary.
I hope I have understood your point.

 

[simon551]

At this point, the time never really becomes part of the billing. We have retainers that don't require time reporting, but want to look at time actuals. The only way I can see to do this is to associate the time data with the client and billing code. This seems like it would be frustrating for the employee to have to enter so many choices (client, billing code, activity, date) I agree with you though that it seems like there are too many tables.

The purpose of the tblRetainers table is to have a table where we hold "default" actuals for the tblActuals table. The retainers can be managed separately then. We may have more than one retainer for the same client. And we may provide services outside the retainer for a retainer client, or a different client.

From your comment and thinking about this, I'm starting to think I need even another table; tblBillingDetails.

Another problem I'm running into with this is: how do people handle internal work? Is there a better way than setting up a "client" of "<No Client>" and a "billing code" of "<internal work>". I hate the sound of that.

Thanks for your feedback.

 

[lespaul]

have you read the fundamentals document below? You need to start by identifing your entities (these will become tables: Clients, Attorneys) and then which attributes (these will become fields: Name, Address) belong to which entities.

By looking at all the data you need to collect and correctly determining what entity that piece of data belongs to, you will have a much easier time developing the table structure and subsequently maintaining the structure will be easier.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi