create relational database

[Ciprian]

Hi guys, i needed to create a bd to keep track of contracts.

So i have

For Contracts : ContractID, ResponsableP, Client

For Addendums to the Contreacts : AddendumID, Faze_Number

Fields that both Contracts and Addendums have in common : Value, Star_Date, End_Date, Invode_Number, Invoice_Date, Invoice_Value, Balance

The approach I took was to make 2 tables :

Contracts
ContractID (Primary key)
ResponsableP
Client
Value,
Star_Date
End_Date
Invode_Number
Invoice_Date
Invoice_Value
Balance

Addendums
AddendumID (Primary key)
ContractID (Foreign Key)
Faze_Number
Value,
Star_Date
End_Date
Invode_Number
Invoice_Date
Invoice_Value
Balance

After some testing, I don't think this was the best way to go. I'm thinking a 3'rd table with the common fields to link those 2, but I'm not really sure how to do that correctly.

Thanks for the advice

 

[KenHigg]

Looks like the contracts -> addendum is correct but I think you may need some other tables for clients and invoices.

 

[Ciprian]

the client field is there only to give a name to the contract, since some ppl know the contract by the ContractID and others by the client name. About the invoices i think you're right, that's why i was looking for any tips on how to modify the 2 tables.

the thing is that although the invoice field are common to both tables, the data in them is different for every table

So a contract will have a Value for example and the Addendum specific to that contract will have a different Value.

 

[GinaWhipp]

My two cents...

tblContracts
cContractID (Primary Key)
cResponsibleParty
cClientID
cValue (What is for?)
cStartDate
cEndDate
cInvoiceID (Foreign Key)

tblAddendums
aAddendumID (Primary Key)
aContractID (Foreign Key - link to tblContracts)
aFazeID
aValue (Again, what does value hold)
aStartDate
aEndDate
aInvoiceID (Foreign Key - link to tblInvoices)

tblInvoices
iInvoiceID (Primary Key)
iInvoiceDate

1. What is stored in Invoice Value?
2. Balance is a calculation and can be calculated on the fly in a query, no need to store in a table.
3. Do Invoices have more than one detail? Is yes you're going to need a tblInvoiceDetail.
4. It would also help to know what business need this database is going to address. Aftwerall, we want to get this Data Model right the first time.

 

[KenHigg]

the client field is there only to give a name to the contract, since some ppl know the contract by the ContractID and others by the client name.

Should you still consider a client/contract table so you will not have to worry about typing in a clientid or name erroneously...

 

[boblarson]

Should you still consider a client/contract table so you will not have to worry about typing in a clientid or name erroneously...

I agree with Ken - create a client table so that the client can be added ONCE so that if they ever are needed again, the selection can be made.

tblClients
ClientID - Autonumber (PK)
ClientName - Text
...any other fields that would be good to keep info about them.

 

[Ciprian]

My two cents...

1. What is stored in Invoice Value?
2. Balance is a calculation and can be calculated on the fly in a query, no need to store in a table.
3. Do Invoices have more than one detail? Is yes you're going to need a tblInvoiceDetail.
4. It would also help to know what business need this database is going to address. Aftwerall, we want to get this Data Model right the first time.

1. Value = Cost, how much the client pays for the services provided by the contract or addendum
2. I was planning to write an update query which will always update the value of the balance
3. Yes Invoices have more details: InvoiceValue, InvoiceDate, iPaymentDate
4. I need this to keep track of the contracts made by the company, especially for the invoices, so that i could find out easily which contract comes to an end and if the client has payed in full or still has payments to do.

 

[jdraw]

There are some free data models at
http://www.databaseanswers.org/data_models/index.htm that may be helpful.

You may get ideas on additional entities ( you mentioned payments) and how they "fit".
Others will also tell you that getting the structures right is a major part of database. It makes life easier with normalized structures.
Good luck.

 

[boblarson]

2. I was planning to write an update query which will always update the value of the balance.

Not a wise thing to do. You should store the data in a way so that you can use a query at any time to calculate that current value.

 

[GinaWhipp]

Amendment...

tblContracts
cContractID (Primary Key)
cResponsibleParty
cClientID (Foreign Key - link to tblClients)
cCost
cStartDate
cEndDate
cInvoiceID (Foreign Key - link to tblInvoices [However if more than one Invoice can be attatched to a Contract you will need a junction table])

tblAddendums
aAddendumID (Primary Key)
aContractID (Foreign Key - link to tblContracts)
aFazeID
aValue (Again, what does value hold)
aStartDate
aEndDate
aInvoiceID (Foreign Key - link to tblInvoices)

tblInvoices
iInvoiceID (Primary Key)
iInvoiceDate
iCost

tblInvoicePayments
ipID (Primary Key)
ipPaymentDate
ipAmount
etc...

tblClients (Everyone is right you should have one of these tables)
cClientID
cClient
etc...

2. No UPDATE query. This will be a calcualted field and the query will handle that for you.

3. What I meant by more details is are there multiple Line Items?

If Contratcs have more than one Line Item you will need tblContractDetails.

 

[Ciprian]

Hi, what do you think of the db ? (I attached a jpeg)
Thanks for the input.

 

[Ciprian]

so it seems i have some problems with the relation between tblAddendumDetails and tblAdvanceInvoices and tblFinalInvoices.

I deleted the relationships between those tables to see how entering Data for Contracts works and it's fine. Now when i want to connect tblAddendumDetails to any of the 2 Invoices tables i get an error about referential integrity.

Any tips on what i did wrong ?

PS. Can't both Contracts and Addendums use the Invoices tables ?

edit: added the database in the attachment

 

[spikepl]

You need to think a bit : a 1-to-1 relation means that for the item on the left side there can only exist one and only one on the right side. Look at your jpeg - it shows things that are either impossible or make no sense. Can one client appear only ever in one contract? You have accomplished this by disallowing duplicates for cClientID. Normally there would be 1-to-many from tblClients to tblContracts, allowing one client more than one contract. And what is the purpose of clID in tblClients? I don't see any.

The above applies to many of your other tables. I would suggest a rethink.

 

[Ciprian]

You need to think a bit : a 1-to-1 relation means that for the item on the left side there can only exist one and only one on the right side. Look at your jpeg - it shows things that are either impossible or make no sense. Can one client appear only ever in one contract? You have accomplished this by disallowing duplicates for cClientID. Normally there would be 1-to-many from tblClients to tblContracts, allowing one client more than one contract. And what is the purpose of clID in tblClients? I don't see any.

The above applies to many of your other tables. I would suggest a rethink.

ok the clients thing can be fixed easily, still this does not help me with my problem nad clID is an autonumber that i want to have there

1 Contract can have 1 AdvanceInvoice which can be paid in full with many small payments

1 Contract can have 1 FinalInvoice which can be paid in full with many small payments

1 Contract can have many Addendums

1 Addendum can have many Fazes

1 Addendum Faze can have 1 AdvanceInvoice which can be paid in full with many small payments

1 Addendum Faze can have 1 FinalInvoice which can be paid in full with many small payments


so other than the clients table where did my logic fail ?

my problem is that i can't use the Invoices tables for both Contracts and Addendums and i want to know what i did wrong or what i need to change to make it work

 

[spikepl]

Let's start at the beginning and worry about the rest later, because there are some conceptual problems at play here.

ok the clients thing can be fixed easily, still this does not help me with my problem nad clID is an autonumber that i want to have there

What is the point of clID other than "I want to have it there"? If it is so that clients get ID's other than some autogenerated number then that's fine. But then the ClientID should not be the primary key, and should not be used for relations, and should be called ClientNumber or something. The clID should be used for relations. The clientID, whatever that is, can then be retrieved from that table at will and only cliD should be a foreign key in other tables.

One you get this sorted out then we can proceed with the rest.

 

[Ciprian]

Let's start at the beginning and worry about the rest later, because there are some conceptual problems at play here.



What is the point of clID other than "I want to have it there"? If it is so that clients get ID's other than some autogenerated number then that's fine. But then the ClientID should not be the primary key, and should not be used for relations, and should be called ClientNumber or something. The clID should be used for relations. The clientID, whatever that is, can then be retrieved from that table at will and only cliD should be a foreign key in other tables.

One you get this sorted out then we can proceed with the rest.

ok, i fixed the clients table and yes all the others ID that are not PK are for the autonumber

what's next ?

 

[spikepl]

1 Contract can have 1 AdvanceInvoice which can be paid in full with many small payments

1 Contract can have 1 FinalInvoice which can be paid in full with many small payments

1 Addendum Faze can have 1 AdvanceInvoice which can be paid in full with many small payments

1 Addendum Faze can have 1 FinalInvoice which can be paid in full with many small payments


so other than the clients table where did my logic fail ?

The above is faulty in that if one and only Advance/Final Invoice belongs to a contract, then it cannot at the same time belong to one addendum phase, because many addenum phases also belong to one contract.

 

[Ciprian]

yes, but Addendum is something extra do the contract and each Faze of the Addendum has it's own Start and End Date and cost.

so that's why i have different Invoices for Contracts and Addendum Fazes

when i design the db, i thought that access will know how to differentiate between the 2 FK linked to the AdvanceInvoices table.

for example
Contract ............ .........100 (is the value for the AdvanceInvoice)
Addendum Faze ........1001 (is the value for the AdvanceInvoice)

so in the AdvanceInvoices, access would know that 100 links the details for the Contracts and 1001 links the detalis for the Addendum Faze, but it seems it doesn't work like that


A Contract and Addendum will never have the same Invoice ID

 

[spikepl]

Sorry I have to take care of my own business for now , but will come back in some 6 hours. Meanwhile - anybody else feel free to jump in.

 

[Ciprian]

Sorry I have to take care of my own business for now , but will come back in some 6 hours. Meanwhile - anybody else feel free to jump in.

oke, thanks for taking an interest