Re: normalised designs: customer database
Jerry Schwartz wrote:
> If all you want to do is to restrict a field to certain values, and aren't
> concerned with cascading operations, is a set more efficient than a foreign
> key?
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>
>
>
>
>
>
Hey,
If efficiency were an issue for 90% of the applications out there, we'd
all be in big trouble. This isn't kernels or apache servers we're
discussing.
What is important, especially to a small contractor such as myself, is
ease of design, implementation and debugging.
I've used the enum approach in the past. Here's what the code for a
typical query build looks like:
-
$query .= 'CASE ';
$query .= 'WHEN ' . getTable('content') . ".`size` = '%s' THEN (" .
getTable('content_strings') . '.`language`) ';
$query .= 'WHEN ' . getTable('content') . ".`size` = '%s' THEN (" .
getTable('content_text') . '.`language`) ';
$query .= "ELSE '' ";
$query .= 'END AS `label_language`, ';
$args[] = ContentItem::SIZE_STRING;
$args[] = ContentItem::SIZE_TEXT;
--
(This is for any text item that can be translated)
And repeat for each and every 'multiplexed' item you require.
Apart from the incredibly complex queries this produces, this also
ensures many hours spent debugging.
In the solution I came up with, I wouldn't have to go through this.
I query from the customers table what I need, and only when the time
comes that I'm printing the actual invoice (for instance), I make
further queries to the differentiated tables for customer types.
All data neatly wrapped into it's own design structure, as the lord god
intended.
You may say that the above query is overly complex and futile, and that
different approaches are better. I would agree with you, were it not for
the fact that I like to program with a 'framework mindset': I want my
code to be reusable and not too complex for possible future collaborators.
Best regards,
Stijn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> >> If all you want to do is to restrict a field to certain values, and > >aren't > >> concerned with cascading operations, is a set more efficient than a > >foreign > >> key? > > > >A Set or Enum? > > > >I can understand "enum"s, but sets are evil. > > > [JS] Why is that? I've been using sets, but I could use enums. Set just > seemed more appropriate for a small (two or three) number of values. Besides the fact that they're non-portable, I don't think they can be properly indexed either, that is, if they contain multiple possible values of the set. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: normalised designs: customer database
>-Original Message- >From: Martijn Tonies [mailto:[EMAIL PROTECTED] >Sent: Monday, November 17, 2008 12:13 PM >To: 'mysql' >Subject: Re: normalised designs: customer database > > > >> If all you want to do is to restrict a field to certain values, and >aren't >> concerned with cascading operations, is a set more efficient than a >foreign >> key? > >A Set or Enum? > >I can understand "enum"s, but sets are evil. > [JS] Why is that? I've been using sets, but I could use enums. Set just seemed more appropriate for a small (two or three) number of values. >Martijn Tonies >Database Workbench Lite for MySQL - FREE developer tool for MySQL! >Upscene Productions >http://www.upscene.com >My thoughts: >http://blog.upscene.com/martijn/ >Database development questions? Check the forum! >http://www.databasedevelopmentforum.com > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> If all you want to do is to restrict a field to certain values, and aren't > concerned with cascading operations, is a set more efficient than a foreign > key? A Set or Enum? I can understand "enum"s, but sets are evil. Martijn Tonies Database Workbench Lite for MySQL - FREE developer tool for MySQL! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: normalised designs: customer database
If all you want to do is to restrict a field to certain values, and aren't concerned with cascading operations, is a set more efficient than a foreign key? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> >> I may just have had an insight over my morning coffee. > >> How about turning things around and adding a FK -to the customers table- > >> on each of the customer type tables (companies, people, charities, etc) ? > >> > >> The customers table would have no idea if a customer is corporate or > >> private, it just has a customer number that can be used in processing > >> invoices and performing account maintenance. > >> The companies, people, charities, etc. tables would each have a FK to > >> the customers table. > >> > >> This does off course mean that creating and sorting a list of all > >> customers is more complex, but the database would at least be normalised. > >> What do you think ? > >> > >> > > I think you just made my point. > > > > You now recognize that designing it "right" introduces other complexities. > > > > With the problem you presented, it is just a matter of where you want to get > > tasered. There isn't a solution that optimizes all parameters. > > > > > Hey, > > I have to disagree. > Any application is and always will be complex. > Having the database refuse to screw itself up whenever the programmer > makes a mistake, and he/she always will, is a great step towards the > goal of simplification and robustness. Exactly. > Moreover, apart from the sorting problem in this design, I think the set > of queries in general is much more simple than it would have been had I > used one of the options from my previous line of thinking. > I think you can never go wrong with normalized databases. Because you can query a table and get data for which you know what it means instead of having to "guess" that columns are used and what not. In general, you should not have NULL values in your database (as opposed to NULLs in your result-sets, that's quite different!). There's no point in storing what you don't know. > Anyway, I think my question has been answered. Always nice to answer > your own questions :) > Thanks for all the comments. Yep, you're setup seems to be the correct one on this, define the properties for a "customer" that are common and put those in the CUSTOMERS table, makes perfect sense. It could be wise to keep track of what type of customer it is in that table as well (so you don't have to check all other tables for it) and keep in mind that a customer can go from a "person" to a "company" etc... Good luck. Martijn Tonies Database Workbench Lite for MySQL - FREE developer tool for MySQL! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
>>> >The notion of a "variant record" exists in many programming languages. >>> >Typically you have a selector to indicate which variant it is. There is >>> >nothing at all wrong with using the same sort of construct in a database >>> >table. >>> >http://en.wikipedia.org/wiki/Variant_record >>> >>> In O-O databases. I think the concept is not defined in relational >>> database theory. Are you aware of the rel db rule regarding domains? >>> >>> >The only constraint you _really_ need to meet in a database is that >>> you let >>> >the database product do the things it needs to do so that the queries >>you >>> >make are O(log N) when possible. The rest is pure fluff. Beyond that, >>> >there is no "should". >>> >>> Relational theory says otherwise. >> >> >>I'm with Peter on this one, in relational theory and data modelling, there's >>a lot of very well documented "should" :-) > >You guys have been reading too many books. Books are bad. >The key question is when something is so much different from another thing that >it is a qualitative difference rather than a quantitative difference. > >I go to the vet's office from time to time, and they keep their records in a computer. > Is a rabbit different enough from a dog that you need different types of records in > the database? Probably not. They are both pets. There may be some information >about dogs that doesn't apply to rabbits or vice-versa, but people make do. You > leave the "rabies vaccination" fields blank for a rabbit, presumably. The notion of a "rabies vaccination" -column- is a bad thing, if this is your design, you've gone the wrong path. Any decent database design book will tell you that, perhaps books aren't that bad? >The "variant record" notion applies--whether it is called that or not--in nearly >every practical database in the world. It still doesn't apply. >A customer that is a person vs. a customer that is a company ... not that different. Right... >I do understand the points that are being made ... but once you get beyond the >database product being able to make queries efficiently ... it is all fluff. Fix the product, keep the design proper. Yet, it's not just about the database product itself, it's the idiocracies you'll be introducing when your design goes haywire... Martijn Tonies Database Workbench Lite for MySQL - FREE developer tool for MySQL! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
Jujitsu Lizard wrote: > On Sat, Nov 15, 2008 at 3:54 AM, metastable <[EMAIL PROTECTED] > >> wrote: >> > > > >> I may just have had an insight over my morning coffee. >> How about turning things around and adding a FK -to the customers table- >> on each of the customer type tables (companies, people, charities, etc) ? >> >> The customers table would have no idea if a customer is corporate or >> private, it just has a customer number that can be used in processing >> invoices and performing account maintenance. >> The companies, people, charities, etc. tables would each have a FK to >> the customers table. >> >> This does off course mean that creating and sorting a list of all >> customers is more complex, but the database would at least be normalised. >> What do you think ? >> >> > I think you just made my point. > > You now recognize that designing it "right" introduces other complexities. > > With the problem you presented, it is just a matter of where you want to get > tasered. There isn't a solution that optimizes all parameters. > > Hey, I have to disagree. Any application is and always will be complex. Having the database refuse to screw itself up whenever the programmer makes a mistake, and he/she always will, is a great step towards the goal of simplification and robustness. Moreover, apart from the sorting problem in this design, I think the set of queries in general is much more simple than it would have been had I used one of the options from my previous line of thinking. I think you can never go wrong with normalized databases. Anyway, I think my question has been answered. Always nice to answer your own questions :) Thanks for all the comments. Best regards, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
On Sat, Nov 15, 2008 at 3:54 AM, metastable <[EMAIL PROTECTED] > wrote: > I may just have had an insight over my morning coffee. > How about turning things around and adding a FK -to the customers table- > on each of the customer type tables (companies, people, charities, etc) ? > > The customers table would have no idea if a customer is corporate or > private, it just has a customer number that can be used in processing > invoices and performing account maintenance. > The companies, people, charities, etc. tables would each have a FK to > the customers table. > > This does off course mean that creating and sorting a list of all > customers is more complex, but the database would at least be normalised. > What do you think ? > I think you just made my point. You now recognize that designing it "right" introduces other complexities. With the problem you presented, it is just a matter of where you want to get tasered. There isn't a solution that optimizes all parameters.
Re: normalised designs: customer database
Martijn Tonies wrote: >> >The notion of a "variant record" exists in many programming languages. >> >Typically you have a selector to indicate which variant it is. There is >> >nothing at all wrong with using the same sort of construct in a database >> >table. >> >http://en.wikipedia.org/wiki/Variant_record >> >> In O-O databases. I think the concept is not defined in relational >> database theory. Are you aware of the rel db rule regarding domains? >> >> >The only constraint you _really_ need to meet in a database is that >> you let >> >the database product do the things it needs to do so that the queries >> > you > >> >make are O(log N) when possible. The rest is pure fluff. Beyond that, >> >there is no "should". >> >> Relational theory says otherwise. >> > > I'm with Peter on this one, in relational theory and data modelling, there's > a lot of very well documented "should" :-) > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase > SQL Anywhere, Oracle & MS SQL Server > Upscene Productions > http://www.upscene.com > My thoughts: > http://blog.upscene.com/martijn/ > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > > I may just have had an insight over my morning coffee. How about turning things around and adding a FK -to the customers table- on each of the customer type tables (companies, people, charities, etc) ? The customers table would have no idea if a customer is corporate or private, it just has a customer number that can be used in processing invoices and performing account maintenance. The companies, people, charities, etc. tables would each have a FK to the customers table. This does off course mean that creating and sorting a list of all customers is more complex, but the database would at least be normalised. What do you think ? Best regards, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
Jujitsu Lizard wrote: > On Fri, Nov 14, 2008 at 1:39 PM, Martijn Tonies <[EMAIL PROTECTED]>wrote: > > >>> >The notion of a "variant record" exists in many programming languages. >>> >Typically you have a selector to indicate which variant it is. There is >>> >nothing at all wrong with using the same sort of construct in a >>> >> database >> >>> >table. >>> >http://en.wikipedia.org/wiki/Variant_record >>> >>> In O-O databases. I think the concept is not defined in relational >>> database theory. Are you aware of the rel db rule regarding domains? >>> >>> >The only constraint you _really_ need to meet in a database is that >>> you let >>> >the database product do the things it needs to do so that the queries >>> >> you >> >>> >make are O(log N) when possible. The rest is pure fluff. Beyond that, >>> >there is no "should". >>> >>> Relational theory says otherwise. >>> >> I'm with Peter on this one, in relational theory and data modelling, >> there's >> a lot of very well documented "should" :-) >> >> > You guys have been reading too many books. Books are bad. > > The key question is when something is so much different from another thing > that it is a qualitative difference rather than a quantitative difference. > > I go to the vet's office from time to time, and they keep their records in a > computer. Is a rabbit different enough from a dog that you need different > types of records in the database? Probably not. They are both pets. There > may be some information about dogs that doesn't apply to rabbits or > vice-versa, but people make do. You leave the "rabies vaccination" fields > blank for a rabbit, presumably. > > The "variant record" notion applies--whether it is called that or not--in > nearly every practical database in the world. > > A customer that is a person vs. a customer that is a company ... not that > different. > It is once you consider creating automated invoices for that customer (e.g. for an online webhosting service or monthly support service fees). You can't deliver this unless a clear distinction is made between a private customer and a corporate customer (and possibly other types of institutions, with other tax rates). > I do understand the points that are being made ... but once you get beyond > the database product being able to make queries efficiently ... it is all > fluff. > > I don't debate that for the problem as stated there are "better" or "more > correct" designs involving more tables. I also don't debate that these > designs are probably more resilient to change and have other advantages. I > agree. > > But you can take your licks in a more complex database design or more > complex code to modify and insert rows and deal with query results. Being > lazy, I'd take my licks in the second way. > > I have the feeling you're a programmer who has learned how to keep his feet on the ground while implementing. Respect ! Best regards, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
On Fri, Nov 14, 2008 at 1:39 PM, Martijn Tonies <[EMAIL PROTECTED]>wrote: > > >The notion of a "variant record" exists in many programming languages. > > >Typically you have a selector to indicate which variant it is. There is > > >nothing at all wrong with using the same sort of construct in a > database > > >table. > > >http://en.wikipedia.org/wiki/Variant_record > > > > In O-O databases. I think the concept is not defined in relational > > database theory. Are you aware of the rel db rule regarding domains? > > > > >The only constraint you _really_ need to meet in a database is that > > you let > > >the database product do the things it needs to do so that the queries > you > > >make are O(log N) when possible. The rest is pure fluff. Beyond that, > > >there is no "should". > > > > Relational theory says otherwise. > > I'm with Peter on this one, in relational theory and data modelling, > there's > a lot of very well documented "should" :-) > You guys have been reading too many books. Books are bad. The key question is when something is so much different from another thing that it is a qualitative difference rather than a quantitative difference. I go to the vet's office from time to time, and they keep their records in a computer. Is a rabbit different enough from a dog that you need different types of records in the database? Probably not. They are both pets. There may be some information about dogs that doesn't apply to rabbits or vice-versa, but people make do. You leave the "rabies vaccination" fields blank for a rabbit, presumably. The "variant record" notion applies--whether it is called that or not--in nearly every practical database in the world. A customer that is a person vs. a customer that is a company ... not that different. I do understand the points that are being made ... but once you get beyond the database product being able to make queries efficiently ... it is all fluff. I don't debate that for the problem as stated there are "better" or "more correct" designs involving more tables. I also don't debate that these designs are probably more resilient to change and have other advantages. I agree. But you can take your licks in a more complex database design or more complex code to modify and insert rows and deal with query results. Being lazy, I'd take my licks in the second way.
Re: normalised designs: customer database
Hi everybody, What about simply storing a main user for each company? So each company has a main user that has the basics about the company ( generic address, email, phone). I would think you'd want to store that information anyways. So the customer table always links to a user. That user could be the company's main user or a user that's not affiliated with a company. There is more general question here as well which would be unavoidable if companies and users were not as closely related. I've seen may implementations like your three suggestions as well as the fake company. If I had to choose one of those, I"d go with the dual foreign keys. #3 Thanks for the meaty question, Bill metastable wrote: Hello all, I have a question that's been bugging me for quite some time. Let's say we have a small business that has both private and corporate customers. We want to store contact and address data about these customers, as well as invoicing data. Off course, only companies have VAT numbers. When normalising this design, you would reach something like the following: - table for contact details (separate, because multiple contact details may apply) - table for address details (separate, because multiple addresses may apply) - table for people (first name, last name, etc) - table for companies (company name and vat number) - tables that link the above data to each other (people-contact, people-address, people-company, company-address, ...) - table for customers, i.e. 'entities' that are invoiced This is where it gets nasty. A customer may be a human being or a company. I see different approaches here: 1) keep customer tables separate, based on which type of customer it is 2) create the customer table with a column specifying if we're dealing with a human being or a company 3) create the customer table with a FK for people and a FK for companies, and decide on the customer type in the application based on the presence of that key Option 1 seems to ridiculous to do: way too much bloody hassle Option 2 may be viable, but we lose the foreign key constraint Option 3 seems like the best idea, but this would mean keeping a table that takes 50% of useless space Neither option satisfies me. My idea right now is a 'False Company' approach: Create a row for a company that is no real company and link all private customers to that company. This way, the customer table has 1 FK, for the companies table. This off course implies that some global variable be present in the application that is used to identify the 'False Company'. Awkward to say the least. What do you guys think about this ? Which option is most viable ? Which solution have you chosen ? And finally: am I going about this all wrong, because I was asleep during most of my formal education years ? :) Kind regards, Stijn -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 (phone) +32 (0)3 707 06 06 (fax) +32 (0)473 47 62 88 (mobile) [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> >The notion of a "variant record" exists in many programming languages. > >Typically you have a selector to indicate which variant it is. There is > >nothing at all wrong with using the same sort of construct in a database > >table. > >http://en.wikipedia.org/wiki/Variant_record > > In O-O databases. I think the concept is not defined in relational > database theory. Are you aware of the rel db rule regarding domains? > > >The only constraint you _really_ need to meet in a database is that > you let > >the database product do the things it needs to do so that the queries you > >make are O(log N) when possible. The rest is pure fluff. Beyond that, > >there is no "should". > > Relational theory says otherwise. I'm with Peter on this one, in relational theory and data modelling, there's a lot of very well documented "should" :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
Jujitsu Lizard wrote: >The notion of a "variant record" exists in many programming languages. >Typically you have a selector to indicate which variant it is. There is >nothing at all wrong with using the same sort of construct in a database >table. >http://en.wikipedia.org/wiki/Variant_record In O-O databases. I think the concept is not defined in relational database theory. Are you aware of the rel db rule regarding domains? >The only constraint you _really_ need to meet in a database is that you let >the database product do the things it needs to do so that the queries you >make are O(log N) when possible. The rest is pure fluff. Beyond that, >there is no "should". Relational theory says otherwise. PB - Jujitsu Lizard wrote: On Fri, Nov 14, 2008 at 10:45 AM, Martijn Tonies <[EMAIL PROTECTED]>wrote: This is where it gets nasty. A customer may be a human being or a company. I see different approaches here: 1) keep customer tables separate, based on which type of customer it is 2) create the customer table with a column specifying if we're dealing with a human being or a company 3) create the customer table with a FK for people and a FK for companies, and decide on the customer type in the application based on the presence of that key You're making it more complicated than it needs to be. A customer may be either a person or a company. Your customers table may contain columns that are the union of what is required for a person and what is requried for a company, plus of course an enumerated value that indicates which the customer is and indirectly which columns are populated for a given row. Problem solved. Over time, several square millimeters on a disk wasted. Back to "database design" class for you ;-) Store in a table what you need to store, storing "which columns are populated for a given row." is complete rubbish. By retrieving data you should -know- what data it is, not having to retrieve a value that indicates what the data actually means. The use of "should" is a bit tricky. By whose standard? The notion of a "variant record" exists in many programming languages. Typically you have a selector to indicate which variant it is. There is nothing at all wrong with using the same sort of construct in a database table. http://en.wikipedia.org/wiki/Variant_record The only constraint you _really_ need to meet in a database is that you let the database product do the things it needs to do so that the queries you make are O(log N) when possible. The rest is pure fluff. Beyond that, there is no "should". The nature of the problem presented by the OP is that all possible solutions are awkward in one way or another. It is just a question of where you want to take the awkwardness. There is no right answer for this problem. You've chosen the gal who can cook but would crack a mirror. I choose the one who looks great but can't cook. Neither is the superior choice. "Should" in the sense you intended it only exists if there is a gal out there who looks great and can cook well ... I don't see it in this case. There are some problems where there is a "right" answer ... but I don't see it in this case because there is complexity introduced no matter which direction you go. Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
Re: normalised designs: customer database
On Fri, Nov 14, 2008 at 10:45 AM, Martijn Tonies <[EMAIL PROTECTED]>wrote: > > > > This is where it gets nasty. A customer may be a human being or a > > > company. I see different approaches here: > > > 1) keep customer tables separate, based on which type of customer it is > > > 2) create the customer table with a column specifying if we're dealing > > > with a human being or a company > > > 3) create the customer table with a FK for people and a FK for > > > companies, and decide on the customer type in the application based on > > > the presence of that key > > > > > You're making it more complicated than it needs to be. > > > > A customer may be either a person or a company. > > > > Your customers table may contain columns that are the union of what is > > required for a person and what is requried for a company, plus of course > an > > enumerated value that indicates which the customer is and indirectly > which > > columns are populated for a given row. > > > > Problem solved. Over time, several square millimeters on a disk wasted. > > Back to "database design" class for you ;-) > > Store in a table what you need to store, storing "which columns are > populated > for a given row." is complete rubbish. > > By retrieving data you should -know- what data it is, not having to > retrieve > a value that indicates what the data actually means. The use of "should" is a bit tricky. By whose standard? The notion of a "variant record" exists in many programming languages. Typically you have a selector to indicate which variant it is. There is nothing at all wrong with using the same sort of construct in a database table. http://en.wikipedia.org/wiki/Variant_record The only constraint you _really_ need to meet in a database is that you let the database product do the things it needs to do so that the queries you make are O(log N) when possible. The rest is pure fluff. Beyond that, there is no "should". The nature of the problem presented by the OP is that all possible solutions are awkward in one way or another. It is just a question of where you want to take the awkwardness. There is no right answer for this problem. You've chosen the gal who can cook but would crack a mirror. I choose the one who looks great but can't cook. Neither is the superior choice. "Should" in the sense you intended it only exists if there is a gal out there who looks great and can cook well ... I don't see it in this case. There are some problems where there is a "right" answer ... but I don't see it in this case because there is complexity introduced no matter which direction you go.
Re: normalised designs: customer database
> >> I wouldn't try to arbitrarily normalise the database for SQL > >> efficiency. > >> In a real-life situation, it's more important that the database > >> design > >> reflects your actual workflow and business requirements. Having a > >> field > >> that's empty 50% or more of the time is far less of a problem than > >> not > >> being able to process a sale because your database structure is too > >> inflexible :-) > >> > > > > I agree. Remember hardware is cheap, software expensive. It's always > > cheaper to add more hard disks than it is to create the software to deal > > with an inflexible design. > > > > > > > Thank you both for your comments. > > I agree that reality will eventually force me to settle for the > hardware-, not software-expensive solution. > > It was more of a theoretical question. I've been researching this on and > off for quite some time now, and have never found a definitive resource > for solving this kind of problem. > Apparently, not ever everyone lies awake of these kinds of things :) > > In addition: I believe in the ultimate flexibility of fully normalised > design over a solution that is cheaper to implement. The latter may work > for a long time, but if/when the day comes that your requirements change > or hacks need to be applied, I would think that a normalised database > will allow you to upgrade the design much more easily than a > non-normalised one. Of course, a de-normalized design makes it much harder to keep track of the data you're using. So yes, I think a proper normalized design is more flexible. Martijn Tonies Database Workbench Lite for MySQL - FREE developer tool for MySQL! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> 3) create the customer table with a FK for people and a FK for > companies, and decide on the customer type in the application based > > >> on > >> > the presence of that key > > > >>> [JS] I'm not sure why you need a foreign key. Surely you won't be > >>> > >> entering > >> > >>> customers using the MySQL CLI client on a routine basis, so your user > >>> interface could (and should) be responsible for checking the data. > >>> > >> Ehm, no, if it's possible, put the constraints -on the database- ... > >> Never > >> ever rely on the application alone to enforce data consistency. > >> > >> > > [JS] I understand your point, but in real life that can lead to a user > > seeing ugly, incomprehensible error messages. Error messages should be handled, obviously, but that is besides the point. > > What I do, in many cases, is provide a dropdown whose values are populated > > from a table of possible values. I suppose you could use that same table to > > enforce foreign key constraints as well, but isn't the effect the same? No, including this part below: > In any event, all data and constraints will be checked in the > application, prior to inserting/updating the database. > It would however be nice to have the db error out in case the > application misses something. It's an awful lot of trouble to weed out > badly inserted or updated records in case of mistakes. > On top of that, the application is only under my control until I become > a millionaire and hire a monkey to code it for me. Your constraint exist because of the programmer making mistakes in his code. Additionally, add-on applications like imports, 3rd party applications etc can make things much harder. When the constraints are on the database, you're at least sure the consistency is OK. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
Mr. Shawn H. Corey wrote: > On Fri, 2008-11-14 at 14:30 +, Mark Goodge wrote: > >> I wouldn't try to arbitrarily normalise the database for SQL >> efficiency. >> In a real-life situation, it's more important that the database >> design >> reflects your actual workflow and business requirements. Having a >> field >> that's empty 50% or more of the time is far less of a problem than >> not >> being able to process a sale because your database structure is too >> inflexible :-) >> > > I agree. Remember hardware is cheap, software expensive. It's always > cheaper to add more hard disks than it is to create the software to deal > with an inflexible design. > > > Thank you both for your comments. I agree that reality will eventually force me to settle for the hardware-, not software-expensive solution. It was more of a theoretical question. I've been researching this on and off for quite some time now, and have never found a definitive resource for solving this kind of problem. Apparently, not ever everyone lies awake of these kinds of things :) In addition: I believe in the ultimate flexibility of fully normalised design over a solution that is cheaper to implement. The latter may work for a long time, but if/when the day comes that your requirements change or hacks need to be applied, I would think that a normalised database will allow you to upgrade the design much more easily than a non-normalised one. Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
US Data Export wrote: >> -Original Message- >> From: Martijn Tonies [mailto:[EMAIL PROTECTED] >> Sent: Friday, November 14, 2008 10:44 AM >> To: 'mysql' >> Subject: Re: normalised designs: customer database >> >> >>>> 3) create the customer table with a FK for people and a FK for >>>> companies, and decide on the customer type in the application based >>>> >> on >> >>>> the presence of that key >>>> >>>> >>> [JS] I'm not sure why you need a foreign key. Surely you won't be >>> >> entering >> >>> customers using the MySQL CLI client on a routine basis, so your user >>> interface could (and should) be responsible for checking the data. >>> >> Ehm, no, if it's possible, put the constraints -on the database- ... >> Never >> ever rely on the application alone to enforce data consistency. >> >> > [JS] I understand your point, but in real life that can lead to a user > seeing ugly, incomprehensible error messages. > > What I do, in many cases, is provide a dropdown whose values are populated > from a table of possible values. I suppose you could use that same table to > enforce foreign key constraints as well, but isn't the effect the same? > > > > > > In any event, all data and constraints will be checked in the application, prior to inserting/updating the database. It would however be nice to have the db error out in case the application misses something. It's an awful lot of trouble to weed out badly inserted or updated records in case of mistakes. On top of that, the application is only under my control until I become a millionaire and hire a monkey to code it for me. Stijn / / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: normalised designs: customer database
>-Original Message- >From: Martijn Tonies [mailto:[EMAIL PROTECTED] >Sent: Friday, November 14, 2008 10:44 AM >To: 'mysql' >Subject: Re: normalised designs: customer database > >> >3) create the customer table with a FK for people and a FK for >> >companies, and decide on the customer type in the application based >on >> >the presence of that key >> > >> [JS] I'm not sure why you need a foreign key. Surely you won't be >entering >> customers using the MySQL CLI client on a routine basis, so your user >> interface could (and should) be responsible for checking the data. > >Ehm, no, if it's possible, put the constraints -on the database- ... >Never >ever rely on the application alone to enforce data consistency. > [JS] I understand your point, but in real life that can lead to a user seeing ugly, incomprehensible error messages. What I do, in many cases, is provide a dropdown whose values are populated from a table of possible values. I suppose you could use that same table to enforce foreign key constraints as well, but isn't the effect the same? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> > This is where it gets nasty. A customer may be a human being or a > > company. I see different approaches here: > > 1) keep customer tables separate, based on which type of customer it is > > 2) create the customer table with a column specifying if we're dealing > > with a human being or a company > > 3) create the customer table with a FK for people and a FK for > > companies, and decide on the customer type in the application based on > > the presence of that key > > > You're making it more complicated than it needs to be. > > A customer may be either a person or a company. > > Your customers table may contain columns that are the union of what is > required for a person and what is requried for a company, plus of course an > enumerated value that indicates which the customer is and indirectly which > columns are populated for a given row. > > Problem solved. Over time, several square millimeters on a disk wasted. Back to "database design" class for you ;-) Store in a table what you need to store, storing "which columns are populated for a given row." is complete rubbish. By retrieving data you should -know- what data it is, not having to retrieve a value that indicates what the data actually means. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
> >3) create the customer table with a FK for people and a FK for > >companies, and decide on the customer type in the application based on > >the presence of that key > > > [JS] I'm not sure why you need a foreign key. Surely you won't be entering > customers using the MySQL CLI client on a routine basis, so your user > interface could (and should) be responsible for checking the data. Ehm, no, if it's possible, put the constraints -on the database- ... Never ever rely on the application alone to enforce data consistency. > If you really feel the need to constrain the data, define the column as a > set. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
On Fri, Nov 14, 2008 at 8:49 AM, metastable <[EMAIL PROTECTED] > wrote: > > This is where it gets nasty. A customer may be a human being or a > company. I see different approaches here: > 1) keep customer tables separate, based on which type of customer it is > 2) create the customer table with a column specifying if we're dealing > with a human being or a company > 3) create the customer table with a FK for people and a FK for > companies, and decide on the customer type in the application based on > the presence of that key > You're making it more complicated than it needs to be. A customer may be either a person or a company. Your customers table may contain columns that are the union of what is required for a person and what is requried for a company, plus of course an enumerated value that indicates which the customer is and indirectly which columns are populated for a given row. Problem solved. Over time, several square millimeters on a disk wasted.
RE: normalised designs: customer database
>-Original Message- >From: metastable [mailto:[EMAIL PROTECTED] >Sent: Friday, November 14, 2008 8:49 AM >To: mysql >Subject: normalised designs: customer database > [JS] My first suggestion, and I am entirely sincere, is that you use either an off the shelf solution or an external service. I inherited a home-grown system and it is a perpetual pain in the neck. It never has done what we really need to do, although it's been getting closer. In a former life I was a consultant, and I always discouraged customers from building when buying would suffice. Of course, every company in the world has unique needs - or so they claim. :-(. 2) create the customer table with a column specifying if we're dealing >with a human being or a company [JS] I recommend this. >3) create the customer table with a FK for people and a FK for >companies, and decide on the customer type in the application based on >the presence of that key > [JS] I'm not sure why you need a foreign key. Surely you won't be entering customers using the MySQL CLI client on a routine basis, so your user interface could (and should) be responsible for checking the data. If you really feel the need to constrain the data, define the column as a set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
On Fri, 2008-11-14 at 14:30 +, Mark Goodge wrote: > I wouldn't try to arbitrarily normalise the database for SQL > efficiency. > In a real-life situation, it's more important that the database > design > reflects your actual workflow and business requirements. Having a > field > that's empty 50% or more of the time is far less of a problem than > not > being able to process a sale because your database structure is too > inflexible :-) I agree. Remember hardware is cheap, software expensive. It's always cheaper to add more hard disks than it is to create the software to deal with an inflexible design. -- Just my 0.0002 million dollars worth, Shawn The map is not the territory, the dossier is not the person, the model is not reality, and the universe is indifferent to your beliefs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
metastable wrote: Hello all, I have a question that's been bugging me for quite some time. Let's say we have a small business that has both private and corporate customers. We want to store contact and address data about these customers, as well as invoicing data. Off course, only companies have VAT numbers. It's more complex than that, because there isn't a clear distinction between individuals and companies. A customer may be a company, a partnership, a charity, a sole trader or a non-trading individual. All but the non-trading individual may have (but are not necessarily required to have) a VAT number. When normalising this design, you would reach something like the following: - table for contact details (separate, because multiple contact details may apply) - table for address details (separate, because multiple addresses may apply) - table for people (first name, last name, etc) - table for companies (company name and vat number) - tables that link the above data to each other (people-contact, people-address, people-company, company-address, ...) - table for customers, i.e. 'entities' that are invoiced What do you guys think about this ? Which option is most viable ? Which solution have you chosen ? What I'd do is have a table for "customer", which is essentially the invoice information (since that is, effectively, what defines a customer to you). I'd have separate tables for alternate addresses and contacts. The customer table would have a field defining the customer type, and another field containing an id which is the key to further information about that customer in the relevant table, where necessary. I wouldn't try to arbitrarily normalise the database for SQL efficiency. In a real-life situation, it's more important that the database design reflects your actual workflow and business requirements. Having a field that's empty 50% or more of the time is far less of a problem than not being able to process a sale because your database structure is too inflexible :-) Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
normalised designs: customer database
Hello all, I have a question that's been bugging me for quite some time. Let's say we have a small business that has both private and corporate customers. We want to store contact and address data about these customers, as well as invoicing data. Off course, only companies have VAT numbers. When normalising this design, you would reach something like the following: - table for contact details (separate, because multiple contact details may apply) - table for address details (separate, because multiple addresses may apply) - table for people (first name, last name, etc) - table for companies (company name and vat number) - tables that link the above data to each other (people-contact, people-address, people-company, company-address, ...) - table for customers, i.e. 'entities' that are invoiced This is where it gets nasty. A customer may be a human being or a company. I see different approaches here: 1) keep customer tables separate, based on which type of customer it is 2) create the customer table with a column specifying if we're dealing with a human being or a company 3) create the customer table with a FK for people and a FK for companies, and decide on the customer type in the application based on the presence of that key Option 1 seems to ridiculous to do: way too much bloody hassle Option 2 may be viable, but we lose the foreign key constraint Option 3 seems like the best idea, but this would mean keeping a table that takes 50% of useless space Neither option satisfies me. My idea right now is a 'False Company' approach: Create a row for a company that is no real company and link all private customers to that company. This way, the customer table has 1 FK, for the companies table. This off course implies that some global variable be present in the application that is used to identify the 'False Company'. Awkward to say the least. What do you guys think about this ? Which option is most viable ? Which solution have you chosen ? And finally: am I going about this all wrong, because I was asleep during most of my formal education years ? :) Kind regards, Stijn -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 (phone) +32 (0)3 707 06 06 (fax) +32 (0)473 47 62 88 (mobile) [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
