> Now I''m using fabForces'' DBDesigner (great, free tool. check it out), > and it insists on adding another foreign key column into the invoice > table: "customer_id". So I want to design my invoice_items table like > I > said above, but DBDesigner wants to do it like this: > > invoice_items > - id > - invoice_id > - customer_id <------- this is what DBDesigner is adding > - description (description of the product) > - value (value of the product)Okay, this is an issue that driving me nuts with Rails apps. I came from the Web design world to Web app development years ago, and am self taught in RDBMS design and administration, so I very well may be throughly confused about this. If so could someone straighten me out: My understanding is that DBDesigner is *CORRECT* !!! One customer, we hope, will buy many products per invoice, and, we hope, will have many invoices over time. So why would this _not_ be a many-to-many relationship that requires a join table, with a concatenated key? To my understanding it''s not an issue of _can_ it be done w/out a join table, but _should_ it be? As I''ve had it explained to me by dba''s this is just basic normalization to avoid bloat and eventually slow queries. But then, I''m just an HTML jockey at heart, so I''m probably confused. Help?! L^Khaos
> My understanding is that DBDesigner is *CORRECT* !!! One customer, we > hope, will buy many products per invoice, and, we hope, will have many > invoices over time. So why would this _not_ be a many-to-many > relationship that requires a join table, with a concatenated key?Customer has invoice, invoice has items. Invoice has one and only one customer. If you want to find the products a customer has purchased you find the invoices. A customer ID on the items is redundant, as there cannot be more than one customer per invoice, and therefore as per 3NF it should not be present.> To my understanding it''s not an issue of _can_ it be done w/out a join > table, but _should_ it be? As I''ve had it explained to me by dba''s this > is just basic normalization to avoid bloat and eventually slow queries. > > But then, I''m just an HTML jockey at heart, so I''m probably confused. > Help?!Third normal form basically says that you shouldn''t duplicate any data. In this case an item in an invoice belongs to an invoice, and an invoice belongs to a customer, therefore the item is attached to a customer through an invoice. This makes the Customer ID in an item redundant, and redundant items should be removed in favour of using existing relationships. -- Phillip Hutchings http://www.sitharus.com/ sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org
On 15.5.2005, at 23:32, Lord Khaos wrote:> >> Now I''m using fabForces'' DBDesigner (great, free tool. check it out), >> and it insists on adding another foreign key column into the invoice >> table: "customer_id". So I want to design my invoice_items table like >> I >> said above, but DBDesigner wants to do it like this: >> >> invoice_items >> - id >> - invoice_id >> - customer_id <------- this is what DBDesigner is adding >> - description (description of the product) >> - value (value of the product) > > > Okay, this is an issue that driving me nuts with Rails apps.What exactly is driving you nuts?> My understanding is that DBDesigner is *CORRECT* !!! One customer, we > hope, will buy many products per invoice, and, we hope, will have many > invoices over time. So why would this _not_ be a many-to-many > relationship that requires a join table, with a concatenated key?Yes and no. There should be a join table (say invoice_items) that connects products to an invoice. But no, it shouldn''t imho have customer_id in it, just invoice_id, product_id and amount (in its most basic form). It also shouldn''t have any product details, they belong to a product table. This is how I would build the db: customers - id - ... invoices - id - customer_id - ... invoice_items - invoice_id - product_id - amount - ... products - id - description - value I would also make invoice_items a class of its own in Rails b/c it is a central part of the application and not just a simple many-to-many relationship between products and invoices: Customer has_many :invoices Invoice belongs_to :customer Invoice has_many :invoice_items InvoiceItem belongs_to :invoice InvoiceItem belongs_to :product Product has_many :invoice_items> > To my understanding it''s not an issue of _can_ it be done w/out a join > table, but _should_ it be? As I''ve had it explained to me by dba''s this > is just basic normalization to avoid bloat and eventually slow queries.I don''t think this was a question about whether or not there should be a join table but whether invoiceitems should be directly connected to customers or not. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On May 15, 2005, at 5:06 PM, Jarkko Laine wrote:> > On 15.5.2005, at 23:32, Lord Khaos wrote: > >> >>> Now I''m using fabForces'' DBDesigner (great, free tool. check it out), >>> and it insists on adding another foreign key column into the invoice >>> table: "customer_id". So I want to design my invoice_items table like >>> I >>> said above, but DBDesigner wants to do it like this: >>> >>> invoice_items >>> - id >>> - invoice_id >>> - customer_id <------- this is what DBDesigner is adding >>> - description (description of the product) >>> - value (value of the product) >> >> >> Okay, this is an issue that driving me nuts with Rails apps. > > What exactly is driving you nuts? > >> My understanding is that DBDesigner is *CORRECT* !!! One customer, we >> hope, will buy many products per invoice, and, we hope, will have many >> invoices over time. So why would this _not_ be a many-to-many >> relationship that requires a join table, with a concatenated key? > > Yes and no. There should be a join table (say invoice_items) that > connects products to an invoice. But no, it shouldn''t imho have > customer_id in it, just invoice_id, product_id and amount (in its most > basic form). It also shouldn''t have any product details, they belong > to a product table. >I agree here, except you may want to throw a product''s price into its invoice entry. Prices change, and a price that changes after an invoice has been created should not change that invoice. -Scott _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 16.5.2005, at 00:12, Scott Barron wrote:> > I agree here, except you may want to throw a product''s price into its > invoice entry. Prices change, and a price that changes after an > invoice has been created should not change that invoice.You''re right, didn''t think that far. The price needs to be in both products table (the base/current price) and invoice lines (the price for that invoice). //jarkko> > -Scott > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails