5

I'm currently working with a project. Now the issue arises is, I have to handle dynamic columns in database table.

I've a table Charges, where amount of different charges corresponding to each Client will be stored. Suppose this table has the following columns-

Client_Id     Charge_1     Charge_2     Charge_3     Charge_4

Now, the administrator can apply more new Charges for clients. In that case, new charges will be added to the Charges table as column. And this need to be handled in application run time not in database design time. This is my thought.

But, it doesn't look suitable to me.

Is there any better idea to handle this issue?? Please suggest me.

Thanks in advance. and I'm new with this database design.

Improve this question

2 Answers 2

Reset to default
7

Make a Composite table, i.e. ClientCharges

You could keep your original Charges Table and your Client table and in the Client Charges table have the following columns:

ClientChargeId, ClientId, ChargeId

In your Charges table you can keep adding (as many Charges are you require) and then reference the ChargeId in the ClientCharges table.

CREATE TABLE ClientCharges
(
    ClientChargeId          INT IDENTITY(1,1)
    , ClientId              INT 
    , ChargeId              INT
)


INSERT INTO ClientCharges
(ClientId, ChargeId)
VALUES
(1, 1),
(1,2),
(1,3),
(1,4),
(2,1),
(3,1),
(3,2),
(4,3),
(4,4)
  • Client 1 has Charges 1, 2, 3 and 4
  • Client 2 has Charge 1
  • Client 3 has charges 1 and 2
  • Client 4 has charges 3 and 4

Then add foreign key constraints on the ClientId and ChargeId fields.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

It sounds like what you really want is a 1 to many table relationship.

What you do is create a table with two columns, Client_id and charge, then you add a new row for each charge.

It's not clear to me how you intend to use this. Are these "charges" individual transactions? Or are they charge types associated with the client? In any event, it's bad form to modify your data model like this.

When you have these kinds of recurring fields, just make them their own table.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.