Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    My Database Design

    There's one question that bothers me all the time,i'll try to explain in it by simple example.
    Lets say i have baskets,i've created table for them:
    BasketID, int (PK)
    BasketNumber, String
    BoxID
    I also gave carton boxes and i've also created tables for them:
    BoxID, int (PK)
    BoxNumber, String
    AppleID
    And i have plenty of apples or i will gather them and the table is:
    AppleID, int (PK)
    NumberofApples, String

    Now what puzzles me is this...i will put at least one apple in each carton box(there's place for 4 apples at all),but i can also put there 2,3 or 4 apples,based on some circumstances.
    And i'll place those boxes then in baskets.The number of boxes in each basket will differ(it can be one box,two,three..ten...twenty and etc).
    Now based on my related tables description it will look something like this:
    BasketID 1
    BasketNumber 1
    BoxID 1


    BoxID 1
    BoxNumber 1

    AppleID 1
    NumberofApples 3

    So by these definitions you can say that there's Basket no.1 in it there's Box no.1 and there's 3 apples in the box itself.

    Everything seems ok here to me,but when i want to add another box to this basket i get puzzled...so how will this look?Since there's one foreign key(BoxID) and i'll relate one more of the same table(BoxID),just probably with another values in there.How will it separate those BoxID collumns one from each other?It can't look like this:
    BasketID 1
    BasketNumber 1
    BoxID 1
    BoxID 2

    BoxID 1 BoxID 2
    BoxNumber 1 BoxNumber 2
    AppleID 1 AppleID 2

    AppleID 1 AppleID 2
    NumberofApples 3 NumberofApples 1

    As i understand,it should be done somehow other way?Since there is appearing two same collumns(BoxID),just the values are different...I can't put two same collumns in Basket definition table(BoxID int (FK),BoxID int (FK)) or can i?
    What am i missing here?
    I'm doing all this with Visual Basic 2008 Express and SQL Express.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I think your mistake is to assume that BoxId should be a foreign key in the Basket table, instead of the other way around. In fact Basket should apparently be an attribute of Box (I am assuming each box into just one basket):

    Code:
    BoxID       BasketID
    ----------- -----------
    1           1
    2           1
    BoxID would be the key of this table.

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    How to solve a problem where lets say there's a lot of companies and their doing some stuff(painting),and those companies can also be not only painters,but sellers,brokers and etc..lots of things?
    It would be like:
    PaintJobID (PK)
    JobName

    CompanyID
    CompanyName
    CompanyPosition (painter,seller,manager...etc,it doesn't really matters).

    And lets say there should be like 5 CompanyPositions in each PaintJob,and same company can be also not in one Positions of the same PaintJob,but few of them:
    PaintJob 1
    JobName MonaLiza

    Company 1
    CompanyName Microsoft
    Position Painter

    Company 1
    CompanyName Microsoft
    Position Seller
    Here you could make PaintJob a FK for Company,right?But later there's gonna be other PaintJobs bt that company...so neither you can make PaintJob FK for company,neither you can make Company FK of PaintJob...
    How do people solve this in design?
    If you would say make them by PaintersID,Sellers,Buyers etc...but in some point same Painter will have more than one PaintJob....and same PaintJob can also have few sellers,brokes,painter...so neither of them fits for FK for each other...
    Really confused here.
    Last edited by Moriarti; 08-20-09 at 15:24.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, what happened to the boxes and baskets? really, if you wish to change schemas like that, please at least have patience while we try to keep up

    what does "5 CompanyPositions in each PaintJob" mean? how can a company be a painter?

    perhaps you are thinking of a many-to-many relationship?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    How about having TWO foreign keys in the Job table: Company and Position.

    A good book on data modelling will cover these kind of examples for you and explain how to solve them.

  6. #6
    Join Date
    Aug 2009
    Posts
    3
    K,i'll try to figure it out...
    Last edited by Moriarti; 08-25-09 at 15:30.

Posting Permissions

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