I can't seem to wrap my head around what should be the primary key in my Portfolio table. I am able to get a quote, buy a stock, and have the price taken from the corresponding user but my Portfolio table does not have a primary key. I read that all tables should have Primary keys and first hand am experiencing why now with index.

I didn't finish CS50 last year and started back up this year at CS50 Finance and it was proven difficult to jump back into so I've gone back and reviewed the previous weeks materials that relate to Python and SQL short of doing the PSETs.

I guess maybe some code would help to show what I have done up to this point when I create my Portfolio table.

rows = db.execute("CREATE TABLE IF NOT EXISTS Portfolio(stock text NOT NULL, shares int NOT NULL, price float NULL, transaction_time NOT NULL)")

Rather than going on and posting code, could someone please point me in the right direction or even provide reading materials? I understand that the user_id can't really be used as a primary key because then the user would not be able to buy a stock more than once. I feel like something like a joint (composite?) Primary Key with the transaction_timeand user_id would work but, how do I bring in the user id? I can't see how the second (maybe third?) tables could work without the user_id being involved somewhere.

  • primary key is a necessity for creating a table. Your can add one more column of user_name or user_id . Then when buying or selling you can just make a sql query to insert the buying or selling transaction details to the current user . Apr 23 '17 at 16:44

portfolio table needs a user_id column, full stop. You need to modify that CREATE TABLE call[1]. It does not necessarily need a primary key. Not every table does. Maybe it just needs a non-UNIQUE index, as suggested in the spec:

Define (non-UNIQUE) indexes on any fields via which you will search (as via SELECT with WHERE).

[1] I would not create the table in the software. Maybe I'm a control freak, maybe I'm old school. Separation of concerns. Use phpliteadmin or sqlite for your DDL (Data Definition Language). Limit the software to DML (Data Manipulation Language).

  • I've managed to get the user into my portfolio table, thanks. I thought the CREATE TABLE call was neat but I understand it may be better to keep it out of the software and in phpliteadmin. Like I said I am still new to this (a year or so moving at snails pace) but, it was something I came across and wanted to see how it worked. I do find myself going back in to phpliteadmin to make the changes anyhow.
    – SozDaneron
    Apr 23 '17 at 21:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .