dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: one to many relationship in sql

  1. #1
    Join Date
    Feb 2006
    Posts
    157

    one to many relationship in sql

    Code:
    CREATE TABLE user (
    id INTEGER NOT NULL,
    name VARCHAR(16) NOT NULL,
    )
    CREATE TABLE order (
    id INTEGER NOT NULL,
    stationary VARCHAR(16) NOT NULL,
    book text,
    forms text
    )
    
    ALTER TABLE user ADD CONSTRAINT user_order FOREIGN KEY (order) REFERENCES order (id)
    
    ALTER TABLE user ADD CONSTRAINT user_order FOREIGN KEY (material,book,form) REFERENCES order (stationary,book,forms)
    I want to create two tables with ''one to many " relationships. Now I am not sure what is the best way to do it. Should I make compound foreign key in the table user containing all attributes from the table order or to make the foregn key in the table user that references the id of the table order.
    Which way is the better one?
    Thanks

  2. #2
    Join Date
    Dec 2009
    Posts
    596

    Re: one to many relationship in sql

    Hi there. I feel this is a tough database design topic for a forum and I definitly wouldn't want to steer you wrong. But I will say this. I wouldn't use text. It's not going to be supported by SQL Server in a future release. That's if this is SQL Server we're talking about.

  3. #3
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: one to many relationship in sql

    Quote Originally Posted by lontana View Post
    I want to create two tables with ''one to many " relationships.
    What you mean is many-to-many relationship.

    You need to create a third table, only contains user en order ids.

    Code:
    CREATE TABLE user_order (
     user_id INTEGER NOT NULL,
     order_id INTEGER NOT NULL
    )
    Primary key should be both ids combined.

    Now, if you want all orders by an user, you can select it by
    Code:
    SELECT order_id
    FROM user_order
    WHERE user_id=123
    Or, the other way around, if you want all users by an order
    Code:
    SELECT user_id
    FROM user_order
    WHERE order_id=123

  4. #4
    Join Date
    Dec 2009
    Posts
    596

    Re: one to many relationship in sql

    We call that third table as an accociative table by the way.

  5. #5
    Join Date
    Feb 2006
    Posts
    157

    Re: one to many relationship in sql

    Thank you for replies.
    Still I have few questions.
    Why "many to many relationship"? I want to make sure that a user can make several orders but also that an order can be made only by one user. How would in this case look the associative table?
    If we have an associative table what is the point of using compound foreign keys?

  6. #6
    Join Date
    May 2009
    Location
    Lincs, UK
    Posts
    298

    Re: one to many relationship in sql

    Quote Originally Posted by lontana View Post
    Why "many to many relationship"? I want to make sure that a user can make several orders but also that an order can be made only by one user.
    You are right, what you want is a one (user) to many (orders) relationship.
    Quote Originally Posted by lontana View Post
    If we have an associative table what is the point of using compound foreign keys?
    The question is: what is the point of using an associative table when you can use a foreign key?
    The foreign key, however, has to be in the orders table, referencing the users table. You'll need to add the user id in the orders table.

  7. #7
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,210

    Re: one to many relationship in sql

    Quote Originally Posted by lontana View Post
    I want to make sure that a user can make several orders but also that an order can be made only by one user.
    In this case you don't need to have a 3rd table. This is a one to many relationship.
    Just add a user id column to the order and make a foreign key:
    Code:
    ALTER TABLE order ADD CONSTRAINT user_order FOREIGN KEY (userid) REFERENCES user (id)
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

  8. #8
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: one to many relationship in sql

    Quote Originally Posted by lontana View Post
    Thank you for replies.
    Still I have few questions.
    Why "many to many relationship"? I want to make sure that a user can make several orders but also that an order can be made only by one user.
    I'm sorry, I misunderstood you.

    Follow the solutions provided by jcaccia and hspc

  9. #9
    Join Date
    Feb 2006
    Posts
    157

    Re: one to many relationship in sql

    Thanks,
    Again I would like to ask whether I can simply skip an associative table and a reference table by using simply a composite foreign key?

  10. #10
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,210

    Re: one to many relationship in sql

    Quote Originally Posted by lontana View Post
    Again I would like to ask whether I can simply skip an associative table and a reference table by using simply a composite foreign key?
    As I clarified in my post, you can skip the associative table, and you don't need a composite key if the id in the User table is the PK.
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

  11. #11
    Join Date
    May 2009
    Location
    Lincs, UK
    Posts
    298

    Re: one to many relationship in sql

    Yes, you just need to make the user id the primary key in the user table, add a user id field in the orders table and create the foreign key. Something like this:
    Code:
    CREATE TABLE user (
    	id INTEGER NOT NULL PRIMARY KEY,
    	name VARCHAR(16) NOT NULL
    );
    
    CREATE TABLE order (
    	id INTEGER NOT NULL,
    	user_id INTEGER NOT NULL,
    	...
    );
    
    ALTER TABLE order ADD CONSTRAINT user_order_fk FOREIGN KEY (user_id) REFERENCES user (id);

  12. #12
    Join Date
    Feb 2006
    Posts
    157

    Re: one to many relationship in sql

    What is the purpose of using a composite key in general?

  13. #13
    Join Date
    Dec 2009
    Posts
    596

    Re: one to many relationship in sql

    A primary key is used to uniquely identify a row and it is also the foreign key to another table. When designing a database one has to decide what will be the information that uniquely identifies the row. One may want to use a natural key instead of a computer generated key for whatever reason. And if the decision is to use a natural key then a combination of columns(composite key) may be in order. When is it appropriate or 'best' to use a composite key? That can be a debate. And I'm sure it has its definite place in the overall scheme of things. But hopefully we won’t get into that!

  14. #14
    Join Date
    Feb 2006
    Posts
    157

    Re: one to many relationship in sql

    My intention of using the composite key was to make sure that a new user is inserted in the user table who makes the order (stationary,book,form) i.e. a row that already exists in the order table. On the other hand I want to prevent that order table have more than one row with same values (stationary,book,form). That can be done with the UNIQUE constraint. But my problem is how to insert a new user that has an order already made. This will clash with unique constraint.

  15. #15
    Join Date
    May 2009
    Location
    Lincs, UK
    Posts
    298

    Re: one to many relationship in sql

    A couple of things don't sound quite right here...

    In general, you will have to have the users first and then they will create orders. Why would you have the order first and then create the user? What if a user wants to create more than one order? If you have a good reason for this, please explain.

    Another thing that doesn't seem rigth is why you would have unique combinations of stationary, book and form in your orders. I don't know what these columns are, but this seems quite restrictive for an orders table (can't different users order the same stuff?).

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)