database table design question
I have three tables (definitions below). My question is there can be multiple "bar" rows per "foo" row. So I need a "fooToBar" table. Is this the right design? What is the name or design pattern of for this type开发者_如何学Go of situation? is there a better name for the table than "fooToBar" table? Thank you very much.
foo
fooID namebar
barID namefooToBar
fooID barIDThe design you name is for n:m relationships. If foo and bar are independent of each other and each foo can be related to several bars - and also each bar can be related to several foos, then this is the approach to go.
If each bar can only be related to one foo, you're in 1:n relationships and could just add a fooID column to the bar table.
I'd say if you keep the naming convention in your whole database, then there's no problem with this name (but correct the capitalization).
If there can be multiple bars for a single foo, but not the other way around, then you have what is called a one-to-many relationship between foo and bar. To model such a relationship you would add a column named fooId
in bar. There would be no need for a fooToBar table.
However, if there can both be multiple bars for a single foo and multiple foos for a single bar then you have a many-to-many relationship between foo and bar, which necessitates a third join table (fooToBar).
Regarding naming convention, I usually use proper casing (not camel casing), and affix each database object with a prefix that identifies the application the object belongs to. Also, I usually name my many-to-many join tables so that they incorporate the names of the other two tables. For example, if I was building Stackoverflow, I might prefix each database object with so, meaning my tables in the many-to-many example would be named:
- so_Foo
- so_Bar
- so_FoosBars
I think what is more important than the naming minutia is that you choose a convention and stick with it.
Happy Programming!
You need the FooToBar table if the foo bar relationship is optional, i.e. if you want to allow Bar rows that have no corresponding Foo row. Otherwise you could just put the foo attribute as a foreign key in bar.
精彩评论