1

I am a software engineer, but I am very new to databases and I am trying to hack up a tool to show some demo.

I have an Apache server which serves a simple web page full of tables. Each row in the table has a proposal id and a link to a web page where the proposal is explained. So just two columns.

----------------------
|  id    |  proposal |
|--------------------
|  1     |  foo.html |
|  2     |  bar.html |
----------------------

Now, I want to add a third column titled Comments where a user can leave comments.

------------------------------------------------
|  id    |  proposal |  Comments               |
|-----------------------------------------------
|  1     |  foo.html | x: great idea !         |
|        |           | y: +1                   |
|  2     |  bar.html | z: not for this release |
------------------------------------------------

I just want to quickly hack up something to show this as a demo and get feedback. I am planning to use SQLite to create a table per id and store the userid, comments in the table. People can add comment at the same time. I am planning to use lock to perform operations on the SQLite database. I am not worried about scaling just want to show and get feedback. Are there any major flaw in this implementation?

There are similar questions. But I am looking for a simplest possible implementation.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
user881300
  • 212
  • 1
  • 9
  • What is your question? how to create comments; how to create a comment field/column; what is the best implementation for a website that includes user comments (which is way too broad and opinionated); or something else? – LinkBerest Oct 31 '15 at 03:14
  • my question is How to implement comments for a web page using sqlite ? – user881300 Oct 31 '15 at 03:53
  • 1
    That of course is hard to say without knowing more about your data etc, but you seem to have a incrementing integer field and that is an obvious candidate. – e4c5 Nov 02 '15 at 10:28

2 Answers2

1

Table per ID; why would you want to do that? If you get a large number of proposals, the number of tables can get out of hand very quickly. You just need to keep an id column in the table to keep track of things and keep the number of tables in a sane figure.

The other drawback of using a table for each proposal is that you will not be able to use prepared statements for those, because table names cannot be bound as a parameter.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • how will I have one to many relationship and query without a full table scan ? If i have a single table where all comments on all proposal sit how can I easily get comments only for proposal I need without doing a full table scan ? – user881300 Nov 02 '15 at 08:36
  • 1
    That is where indexes come into the picture. When you have a proper index only the relevant records are read. – e4c5 Nov 02 '15 at 09:27
  • what will be the primary key of the table that has the comments ? – user881300 Nov 02 '15 at 10:22
  • Index solved the problem of searching just the relevant records – user881300 Dec 28 '15 at 14:33
0

SQLite assumes the table name is 'a'

Add column

alter table a add column Comments text;

Insert comment

insert into a values (4,"hello.html","New Comment");

You need to provide values for the other two columns along with the new comment.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Marichyasana
  • 2,966
  • 1
  • 19
  • 20