locked
One to Many Entity Relationship in SQL Server RRS feed

  • Question

  • Hello,

    Can you please help me out and let me know if the database design below is correct or need to be modified. please ignore the sql syntax

    I have used One to Many relationships between entities as you can see the Database design below:

    1) CREATE TABLE CUSTOMERS(CUSTOMERID CHAR(10) PRIMARY KEY)

    2) CREATE TABLE TRANSPOTERS(TCOMPANYID CHAR(10) PRIMARY KEY)

    3) CREATE TABLE DRIVERS(DRIVERID CHAR(10) PRIMARY KEY)

    4) CREATE TABLE ADVERTS(ADVERTSID CHAR(10) PRIMARY KEY, CUSTOMERID CHAR(10), TCOMPANYID CHAR(10) FORIEGN KEY CUSTOMERID, TCOMPANYID REFERENCES (CUSTOMERS.CUSTOMERID, TRANSPORTERS.TCOMPANYID))

    5) CREATE TABLE BIDS(BIDID CHAR(10) PRIMARY KEY, TCOMPANYID CHAR(10), DRIVERID CHAR(10) FORIEGN KEY DRIVERID, TCOMPANYID REFERENCES (DRIVERS.DRIVERID, TRANSPORTERS.TCOMPANYID))

    Tables Relationships

    (CUSTOMERS & TRANSPOTERS) - ONE TO MANY - ADVERTS

    (TRANSPORTERS & DRIVERS) - ONE TO MANY - BIDS

    BIDS TABLE - MANY TO ONE - ADVERTS

    FOLLOWING ARE THE REQUIREMENTS

    a) customer can place multiple advertisment

    b) transport company can place multiple advertisment

    c) transport company can also place bids on existing advertisment

    d) drivers can place bids on same existing advertisment

    e) one advertisment can have multiple bids (placed by drivers/Transporters)

    f) each BidId refers/belongs to a specfic advertisment

    I am bit confused what happen if there are two parent tables and one child table how to create one to many relationship using forigen key and primary key reference?

    If I keep the primary key in each master table and repective forigen keys in one child table there is a chance that it might hold null values for the first or second parent keys.

    Thanks,


    Abdul Afroz
    • Moved by Tom PhillipsEditor Monday, June 20, 2011 3:48 PM Database Design question (From:SQL Server Database Engine)
    Monday, June 20, 2011 3:38 PM

Answers

  • Hi Abdul,

    In your plan i think one thing is missing, i.e. relation between ADVERTS & BIDS table. As you mentioned in point (C), (D), (E) & (F) there is relation between ADVERT & BIDS table as BID will be against an ADVERTISEMENT.

    Coming back to your second question, it might hold null values for 1st and 2nd parent keys then that is true and i don't think there should be any problem. eg:

    table : ADVERT

    Advertisement 1 opened by Customer 1 so there will be 1 entry and Transport column will be NULL.

    Advertisement 2 opened by Transported 1 so there will be q entry and Customre column will be NULL.

    So i don't think there should be any problem in this. Now the thing is we need to have another table which should be a bridge between ADVERT & BIDS table and its structure should be like this:

    CREATE TABLE ADVERTBIDS (ADVERTSID CHAR(10), BIDID CHAR(10) FOREIGN KEY ADVERTSID,BIDID  REFERENCES (ADVERT.ADVERTSID,BIDS.BIDSID)

    So if we will be having this table it will bridge the gap as we will be having a table which holds informtion as what is the BID associated with a ADVERTISEMENT.

     


    Regards Gursethi Blog: http://sqlfundas.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Monday, June 20, 2011 4:08 PM

All replies

  • Hi Abdul,

    In your plan i think one thing is missing, i.e. relation between ADVERTS & BIDS table. As you mentioned in point (C), (D), (E) & (F) there is relation between ADVERT & BIDS table as BID will be against an ADVERTISEMENT.

    Coming back to your second question, it might hold null values for 1st and 2nd parent keys then that is true and i don't think there should be any problem. eg:

    table : ADVERT

    Advertisement 1 opened by Customer 1 so there will be 1 entry and Transport column will be NULL.

    Advertisement 2 opened by Transported 1 so there will be q entry and Customre column will be NULL.

    So i don't think there should be any problem in this. Now the thing is we need to have another table which should be a bridge between ADVERT & BIDS table and its structure should be like this:

    CREATE TABLE ADVERTBIDS (ADVERTSID CHAR(10), BIDID CHAR(10) FOREIGN KEY ADVERTSID,BIDID  REFERENCES (ADVERT.ADVERTSID,BIDS.BIDSID)

    So if we will be having this table it will bridge the gap as we will be having a table which holds informtion as what is the BID associated with a ADVERTISEMENT.

     


    Regards Gursethi Blog: http://sqlfundas.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Monday, June 20, 2011 4:08 PM
  • If a transporters bids like a customer, why not make him a customer too? (Like would have to happen for drivers)

    Why are drivers and transporters in different tables?

    Monday, June 20, 2011 6:04 PM
    Answerer
  • Hello Gursethi,

    Many Thanks for your reply,

    I actually had the relationship between Adverts and Bids too but looks like I missed that in typing the question inside the Bids table itself I included (BidId, DriverId, TransporterId, AdvertsId) references back to the master tables would that be right or should I have to create a seprate (Junction table) as you suggested? as for as I know Junction table should be used only for many to many relationships?

    Also one important thing am I correct to say its One To Many relationship exits for all my tables or Should it be Many to Many relationship?

    a) Since there will be multiple (drivers /transporters) bidding for multiple advertisments and on the other hand multiple advertisements get bids from multiple drivers/transporters) should it be 1 to many or many to many?

    b) If I see the same scenario from a different angle One (drivers /transporters) can bid for multiple advertisments and similarly multiple bids for an advertisments belongs to an indiviual driver or transpoter? then to me it looks like its one to many?  I m confused please clarify me

    Thanks Once again,

     


    Abdul Afroz

    Tuesday, June 21, 2011 10:06 AM