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)
I also gave carton boxes and i've also created tables for them:
BoxID, int (PK)
And i have plenty of apples or i will gather them and the table is:
AppleID, int (PK)
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:
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:
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.
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):
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:
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:
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.