Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

database design help in mysql

Status
Not open for further replies.

darkarchon

Technical User
May 23, 2003
6
US

hi... just 2-3 days of mysql here... i want to set up a database... it might have about 50-60 columns... basically for the finance industry we have to list creditors for each of our clients. some clients have 4 creditors. some have 12. some have 15.

so what is the best way to design this? just a table with 60 columns in this format:

client creditor1 creditor2 creditor3 creditor4 etc...

basically i am looking if there is a way to create "dynamic columns" or some sort of linked list? or link other tables? i want to be most efficient in my design and if i leave space for 12 creditors for each client then i will have alot of null values.

your comments are helpful and really appreciated.
 
Create an additional table for keeping track of creditors

Code:
create table client(clientId int primary key, ... )

create table creditor(creditorId int primary key , ... )

create table clientCreditor(clientId int references client,
creditorId int references creditor, primary key(clientId,creditorId))
 
swampboogie,

thanks for the reply. but i still have a couple of questions.

i see that you create a table for clients, and another table for creditors. how does the third table link them? what does primary key(clientId,creditorId) do ?

so i could have multiple entries for each client in the clientCreditor table? for e.g. a client with 15 creditors would have 15 different entries?

thanks in advance!
 
ah i get it now. you are marking both clientId and creditorId as primary keys and making this a multiple-column index, but how will this work? i am still a bit confused.
 
You are right that having multiple creditors will result in the same number of entries in the clientCreditor table. By this it is possible to have multiple creditors for a client and creditors with multiple clients.

This part

Code:
references client,

sets up a foreign key relation between the tables. (The exact syntax in Mysql have some additional requirements with regards to indexes, see .) This means that you can not insert values into this table unless they are present in the referenced tables, thus ensuring data integrity.

The typical scenario when adding new clients and creditor would be to first add the client(s) and the creditor(s) and then get the primary keys for these records and then establish the relation by inserting into the clientCreditor table.

I don't how you intend to handle the generation of primary key values but if you define the id columns as counters you can use the mysql function last_insert_id to get the value.
 
i tried it and i get the following error. what is wrong? please help!

CREATE TABLE Creditors_Customers(

Customer_Id bigint( 20 ) NOT NULL default '0',
Creditor_Id bigint( 20 ) unsigned NOT NULL default '0',
Debt_Amount varchar( 20 ) default NULL ,
PRIMARY KEY ( Customer_Id, Creditor_Id ) ,
FOREIGN KEY ( `Customer_Id` ) REFERENCES `Customers` ( `Customer_Id` ) ON DELETE CASCADE ,
FOREIGN KEY ( `Creditor_Id` ) REFERENCES `Creditors` ( `Creditor_Id` )
) TYPE = InnoDB

MySQL said:


Can't create table './admin2_debtsetcustomers/Javaga2.frm' (errno: 150)
 
Some InnoDB expert told me this:


there must be an index where the foreign key and the referenced key are listed as the FIRST columns.

Above the column Creditor_Id is not listed as the FIRST column in the primary key. You have to create another index where it is the first column.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top