-2

I need to create sqlite statement that checks if a specific column value is not null then add a new column and insert the new value.

What I already have is this :

CREATE TABLE "StuPayment" (
    "PayNumber" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "StudentName"   ntext,
    "CourseName"    ntext,
    "PayDate"   datetime,
    "CheckNumber"   NUMERIC,
    "Amount"    NUMERIC
)

What I want to create is a new payment columns (PayDate1,CheckNumber1,Amount1) when a student pays a course cost on two payments or maybe three sometimes. Thanks for your time reading this.

haldo
  • 14,512
  • 5
  • 46
  • 52
Salim Al-Tarsha
  • 1
  • 3
  • 3
    I'd recommend making a new row instead of more columns. Adding a column is going to be a nightmare down the road when you have more and more payments. – dvo Oct 15 '19 at 14:55
  • i thought about that , but the problem is after a year maybe the table can be up to 10000 rows and that's very difficult to filter or create a report , thanks for your advance mate – Salim Al-Tarsha Oct 15 '19 at 14:58
  • 1
    Relational Databases are modeled based upon relationships. Your approach misses that *each student can make many payments* which means many payment rows per student. Which is very simple to filter as needed - date or sequence number for instance – Ňɏssa Pøngjǣrdenlarp Oct 15 '19 at 15:00
  • 1
    10,000 rows is not a lot. You're going to have a worse time maintaining adding columns over and over every time there is a payment. Also, as for reporting, you can do a query using group by and other tools to pull data in the same format you're suggesting. It's just not the best choice to design the table that way. – dvo Oct 15 '19 at 15:01
  • is there a way to group rows sharing the same StudentName and CourseName using sqlite ? – Salim Al-Tarsha Oct 15 '19 at 15:02
  • @SalimAl-Tarsha Yes it is possible. Implementing a student ID is a great way to go and you can group based on that. – dvo Oct 15 '19 at 15:06
  • thanks guys , that was helpfull – Salim Al-Tarsha Oct 15 '19 at 15:43

2 Answers2

1

No. Don't do it. Just record the payments in multiple rows. You already have a datetime column, so each payment is recorded separately.

There are multiple advantages to new rows:

  • You can easily search for things like amount > 1000 and not have to worry about extra columns.
  • You can use an index to search on the payment columns, such as getting all payments on a particular date.
  • PayNumber uniquely identifies each payment.
  • You don't have to reserve space for empty values in all the rows.
  • Adding new payment methods (say credit cards, debit cards, direct debit, or other mechanisms) is simpler, because you don't have to multiply the columns for each potential payment.
  • You can more easily support payment plans, such as one payment per week.

Your concern about 10,000 rows/year is not relevant in today's world. Databases and computers are powerful.

If you want to see all the payments that a student has made, you can use:

select studentname, coursename, count(*) as numpayments, sum(amount)
from stupayment
group by studentname, coursename;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I would recommend to have a view created and do whatever manipulation you want as I see theres unclear logical requirement. Go for view Creation

   Create view as (Select * from table) ;
   Alter view add column c1 ;
Himanshu
  • 3,830
  • 2
  • 10
  • 29