I have a database with two tables, A and B.

Table A contains the title of a book and has an auto-increment column, book_id, that I declared as primary key.

In table B, I want to retain the price of the book having the same book_id value. In this case, the book_id column will be constrained as a foreign key and I dont want to auto-increment it.

How do I do it using PHP?

  • 2
    What RDBMS are you using (as the syntax is going to vary based on SQL implementation)? Forget PHP for a minute and think about how you'd go about doing what you want in just SQL ... then if you're unable to convert a pure SQL solution to PHP, post back here with your SQL code and sample PHP attempts. Also consider reviewing How do I ask a good question? – markp Aug 6 '17 at 15:35

Your schema doesn't even make sense. You need to have an authoritative bookid somewhere.

  bookid  serial PRIMARY KEY
  -- stuff

CREATE TABLE book_title (
  bookid  int REFERENCES book,
  title   text

CREATE TABLE book_price (
  bookid  int REFERENCES book,
  price   float

That said, if everything is 1:1 with book, just put it all on the same table. You don't need a separate table for every column:

  bookid  int serial,
  title   text,
  price   numeric(10,8)
  • 1
    I would counter that 'it depends' on what needs to be stored. If storing historical prices then storing price in a 'history' table may make sense; if the price paid can vary over time, or based on coupons and/or membership levels then price could be stored in an order_item table; if price could vary based on format (hardback, softback, e-book) and/or currency then price could be stored in a separate table based on format/currency; hard to say at this point where price should be saved without a lot of details ... details the OP has not provided eh – markp Aug 6 '17 at 20:08
  • 1
    Good Answer, except for the minor issue of float being the wrong data type for money. – Basil Bourque Aug 7 '17 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.