CODEWITHSUNDEEP
database-design
Matthew Evans
Matthew Evans

Reputation: 13

What method should I use to store many categories of data?

The issue I'm having, is that my app is trying to show a client several months of their invoicing history, and I am unsure of how to save all of the data.

Currently I have all of my Many Clients Data stored in a SQLiteDatabase, and I assume this would be the easiest option, though I have no idea how to go about saving their prior purchases.

Should I continue to use SQLite and just build more columns for each prior charge, or is there a better option?

DB Schema is

TABLE=CUSTOMERS

COLUMN1=ID
COLUMN2=NAME
COLUMN3=ADDRESS
COLUMN4=SPECIAL NOTES

From here the best way I can think of, is adding columns for the Services that were charged, and three copys of them so as to have months prior history. Such as,

Col4month1=Service1
Col5month1=Service2
Col6month1=Service3
Col7month1=Service4

Col8month2=Service1
Col9month2=Service2
Col10month2=Service3
Col11month2=Service4

Surely there is an easier way to do this

Upvotes: 0

Views: 57

Answers (1)

Brian
Brian

Reputation: 1248

If you post your schema, or a representative sample of it, I can tailor this a bit, but based on your description it sounds like you need a child table, as CL suggested. It would look something like this:

Client
    ID              --primary key
    First_Name
    Last_Name
    etc.

Invoice
    ID              --primary key
    Client_ID       --foreign key link to Client
    Invoice_Date
    Invoice_Amount
    etc.

Invoice_Line_Item
    ID              --primary key
    Invoice_ID      --foreign key link to Invoice
    Item_Number
    Quantity
    etc.

With the above structure, a client can have multiple invoices, each invoice can have multiple items, and they all link together on the foreign keys. Does that make sense?

Upvotes: 1

Related Questions