Number of Database Tables
-
I haven't really noticed myself wanting to create more tables as time goes on. I've worked on projects with tens of tables, and projects with hundreds of tables; it depends on the requirements, not so much on my preferences. To me the DB design has always been pretty clear based on the requirements. You know when you are going to need a one-to-many relationship, you know that means another table with a foreign key, these kinds of things are usually pretty straightforward. Not that everything has to be normalized perfectly, but the DB design is usually pretty clear if the application design is worked out. If the DB design isn't clear, then you may not be ready for the DB design. But it sounds like you're talking about leveraging the DB more rather than just how to design the structure. Things like putting drop-down list items in a table makes life so much easier for everyone, storing configuration data and meta data can be great, taking advantage of serialization and document storage in the DB can be a huge boon, etc. But, I've never gotten to the point where I want to put everything in the DB. In my experience web applications always seem to bottleneck at the DB, especially when it comes to table locks and disk I/O, so I've learned to be careful about hitting the DB. Things like storing snapshots of everything may be great at first, and then bring the system to its knees when you suddenly have to scale up. I know it's practically heresy to say something like this these days, but it is possible to have too much data flying around.
-
In my 10 years of development I've noticed that the number of database tables I have in similar applications has more than quadrupled. An application I wrote 10 years ago only has 12 tables whereas my most recent work has 50 tables and I've a dozen yet to add. There are several reasons for this: 1: I've a tendency to use many to many relationships more often. 2: I've a tendency to 'future proof' my applications by adding tables that allow for growth. 3: I've a tendency to put combobox lists into discrete tables so that adding another option is as easy as adding a single row to a table. 4: I've log tables for everything a user may touch - before and after snapshots of everything. 5: I've significantly more items that are configurable. I know most here have applications that run into hundreds of tables. My applications are all very small. That said, have you noticed a sharp increase in the number of tables you author as compared to when you started?
If the preceding respondents' views reflect the current view of database (DB) design, I'm not sure that I agree with the direction that development is going. In my experience with DBs, I believe that the data architect has an obligation to include only those relations (tables) that are required to perform needed functionality against a collection of data. Data collection is normally performed at a very large cost; the primary driver being the data integrity and correctness validations. So a DB is a corporate asset that must be protected. By adding relations that are not part of the corporate data structure (i.e., programming information), not only is the DB compromised but it also becomes confused, and worse, unmaintainable. In fact, the spurious generation of relations flies in the face of Codd's overriding reason for proposing the relational model - simplicity. The difficulties associated with navigating the CODASYL model cannot be understated. So Codd's proposal of a collection of table-like entities was embraced. But I believe that he would object to the kitchen sink attitude that drives the addition of spurious data into a DB. To answer the OP - no. I do not add any extra data to DBs that I might develop. If the program becomes so convoluted that some form of external navigation is required, then the implementation cries for revision.
Gus Gustafson
-
In my 10 years of development I've noticed that the number of database tables I have in similar applications has more than quadrupled. An application I wrote 10 years ago only has 12 tables whereas my most recent work has 50 tables and I've a dozen yet to add. There are several reasons for this: 1: I've a tendency to use many to many relationships more often. 2: I've a tendency to 'future proof' my applications by adding tables that allow for growth. 3: I've a tendency to put combobox lists into discrete tables so that adding another option is as easy as adding a single row to a table. 4: I've log tables for everything a user may touch - before and after snapshots of everything. 5: I've significantly more items that are configurable. I know most here have applications that run into hundreds of tables. My applications are all very small. That said, have you noticed a sharp increase in the number of tables you author as compared to when you started?
Well I'd say your database design is wrong. You cannot have done effective normalisation on your data if you are trying to model many-to-many relationships as this breaks the rules on normalisation and is prone to all sorts of errors in the database. But then again, what do I know?
-
In my 10 years of development I've noticed that the number of database tables I have in similar applications has more than quadrupled. An application I wrote 10 years ago only has 12 tables whereas my most recent work has 50 tables and I've a dozen yet to add. There are several reasons for this: 1: I've a tendency to use many to many relationships more often. 2: I've a tendency to 'future proof' my applications by adding tables that allow for growth. 3: I've a tendency to put combobox lists into discrete tables so that adding another option is as easy as adding a single row to a table. 4: I've log tables for everything a user may touch - before and after snapshots of everything. 5: I've significantly more items that are configurable. I know most here have applications that run into hundreds of tables. My applications are all very small. That said, have you noticed a sharp increase in the number of tables you author as compared to when you started?
MehGerbil wrote:
There are several reasons for this:
At least for me it arises from the need to model more complex relationships and support more complex functionality. That said it is probably also due to less gate keeping by a formal DBA as well. As one moves further into the past the database was more likely to be managed only by a dedicated DBA. Versus now where almost every developer on the team might be sticking something in there. Thus more chaos leads to more tables.
-
Well I'd say your database design is wrong. You cannot have done effective normalisation on your data if you are trying to model many-to-many relationships as this breaks the rules on normalisation and is prone to all sorts of errors in the database. But then again, what do I know?
-
How do you handle groups with many users and users with many groups in a non many-to-many model in a database?
I'm not sure what "not in a many-to-many way" means, but wouldn't you normally use a cross-reference table for that? Technically that's a one-to-many relationship which goes both ways with a special table in between, but that's normally what I think of as being a many-to-many relationship in the DB.
-
How do you handle groups with many users and users with many groups in a non many-to-many model in a database?
Your data should go through at least the first three stages of normalisation: 1NF, 2NF & 3NF; Boyce-Codd NF is PITA! https://en.wikipedia.org/wiki/Database_normalization[^] Think about, say, Amazon. Amazon have many customers and all these customers can buy many things: an M-M relationship. To get around this, normalisation would result in a third table being created of say 'ORDERS'. So, when you order online, you become ONE customer with your order which can be for many ITEMS. This will then be an M-1-M relationship; one customer can have MANY orders, and one order can have MANY items thereby removing the M-M relationship between customers and items. The same logic can be applied in your M-M case. The hardest part is deciding on what that third table is especially if it isn't immediately obvious. Proper database design can really be a PITA. I had to do a customer database for my gaffer at work to model which machines the customer bought, when they've had them serviced and what spare parts have been used. I think I was at the DB design stage at least a week before I could correctly model the data using normalisation. BTW, I'm not a software or database designer by trade; I'm an engineer who codes for a hobby and studied DB design at college to HNC level.
-
Mark_Wallace wrote:
If 1, don't expect any positive feedback, compliments, or bonuses.
I made the changes less for money and fame and more for making my life easier. I had a boss who was an excellent programmer once. He said the primary trait of a good programmer is laziness. Nobody works harder to do nothing than a good programmer.
MehGerbil wrote: He said the primary trait of a good programmer is laziness. Nobody works harder to do nothing than a good programmer. I think I finally found a signature. Would you mind?
-
_Maxxx_ wrote:
Interesting - I can't think of many times I've found many to many relationships of value in my Db design - they seem to be either necessary or not! Care to share an example?
A user may be a member of many groups and a group may have many users. In my first application a user could only be a member of one group - granted, for that first application it worked well enough. It's 10 years old this year.
_Maxxx_ wrote:
I can't get my head around this. DO you mean you might add tables for, say, CustomerVehicle just in case you decide to store that information at a later date?
I'm writing an application now that will route a document to several users. The route the document will take could be hard coded because there will only ever be one route and the steps on the route haven't changed in decades. However, I'm creating a Route table and a Steps table (and other supporting tables) because experience has taught me that a process that hasn't changed in decades will develop a need to change the moment I launch the new application. ;P So although there is no UI to support additional routes and steps (it will appear hard coded to the users) the database structure is already in place to handle multiple routes and the deletion/addition of steps. Doing that now is a minimal investment of time compared to having to convert a live system from a hard-coded implementation to a more dynamic implementation at some point in the future.
_Maxxx_ wrote:
Once implemented an awesome logging system that just logged the changes in a generic table (so it had a key of which table, which column etc.) sounded good in theory but reporting on it was a bitch!
I fell for that trap. I even created log objects and it just turned out to be a big mess - and like you say, a real problem when it came to reporting.
MehGerbil wrote:
A user may be a member of many groups and a group may have many users.
OIC what you mean - yes - I have loads of relationships like that using a 'relationship' table - in your example case a User_Group table. Funny - for some reason I just don't think of them as many to many relationships - must be getting old!
MehGerbil wrote:
will develop a need to change the moment I launch the new application.
Usually shortly after the manager involved has sworn on the life of his children that that process will NEVER change!
MehGerbil wrote:
minimal investment of time compared to having to convert a live system from a hard-coded implementation to a more dynamic implementation
I agree - but I find it to be a balancing act at times; some of our developers seem to want to make everything o generic as its all but unmaintainable - even though the chances of a change are low and the potential cost of making the change to a less generic system quite high.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Nagy Vilmos wrote:
why not a single kitchen sink look up?
SELECT CODE, DESCRIPTION FROM LOOK_UP WHERE TYPE = 'FOO_BAR'?Because given any sizeable application, additional data gets added to LOOK_UP to cater for individual cases - then do you add columns for those cases leaving them null for all other rows? So you end up with a row
TYPE CODE DESCRIPTION IsDefault Minimum Value
COUNTRY USA United States Of America True 0Because your Unit of Measure lookup requires a minimum value. separate tables, OTOH, allow each table to have a single function and be appropriately defined.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
You can have the best of both worlds. For the "run of the mill" comboboxes (in most applications these will be the vast majority) that do not have special requirements use a central table. For any that have special requirements (such as in your example above), use a table specific to that combobox. As you develop you will likely find (in a large application anyway) that you may have more than one group of comboxes with like (special) requirements, in this case you may want to implement multiple combinatory lookup tables (one for each special requirement combobox type). Either way it should reduce the table count (and possibly the SP count if you are using them).
-
MehGerbil wrote: He said the primary trait of a good programmer is laziness. Nobody works harder to do nothing than a good programmer. I think I finally found a signature. Would you mind?
-
MehGerbil wrote:
A user may be a member of many groups and a group may have many users.
OIC what you mean - yes - I have loads of relationships like that using a 'relationship' table - in your example case a User_Group table. Funny - for some reason I just don't think of them as many to many relationships - must be getting old!
MehGerbil wrote:
will develop a need to change the moment I launch the new application.
Usually shortly after the manager involved has sworn on the life of his children that that process will NEVER change!
MehGerbil wrote:
minimal investment of time compared to having to convert a live system from a hard-coded implementation to a more dynamic implementation
I agree - but I find it to be a balancing act at times; some of our developers seem to want to make everything o generic as its all but unmaintainable - even though the chances of a change are low and the potential cost of making the change to a less generic system quite high.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
_Maxxx_ wrote:
OIC what you mean - yes - I have loads of relationships like that using a 'relationship' table - in your example case a User_Group table. Funny - for some reason I just don't think of them as many to many relationships - must be getting old!
I'm not really a database guy so I'm guessing I mis-used the terms somehow. I don't think you're the only person I confused by doing that.
_Maxxx_ wrote:
I agree - but I find it to be a balancing act at times; some of our developers seem to want to make everything o generic as its all but unmaintainable - even though the chances of a change are low and the potential cost of making the change to a less generic system quite high.
Absolutely.
-
In my 10 years of development I've noticed that the number of database tables I have in similar applications has more than quadrupled. An application I wrote 10 years ago only has 12 tables whereas my most recent work has 50 tables and I've a dozen yet to add. There are several reasons for this: 1: I've a tendency to use many to many relationships more often. 2: I've a tendency to 'future proof' my applications by adding tables that allow for growth. 3: I've a tendency to put combobox lists into discrete tables so that adding another option is as easy as adding a single row to a table. 4: I've log tables for everything a user may touch - before and after snapshots of everything. 5: I've significantly more items that are configurable. I know most here have applications that run into hundreds of tables. My applications are all very small. That said, have you noticed a sharp increase in the number of tables you author as compared to when you started?
MehGerbil wrote:
3: I've a tendency to put combobox lists into discrete tables so that adding another option is as easy as adding a single row to a table.
Do you put reference data such as this into its own schema?
MehGerbil wrote:
4: I've log tables for everything a user may touch - before and after snapshots of everything.
Same question here.
-
I have found exactly the same - I go even further, using tables to define parts and behaviour of the user interface. So for data entry I define the fields on a form and how they behave via a table(if they are comboboxes - the stored procedures or values that populate them etc) - that way I can change the behaviour of the application without having to change the application(where the application uses a central back-end database).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I saw a system last year that had a table, tblGender, to hold the valid values for gender. There was a single column. There were two rows, containing the texts "F" and "M". There was a second table, tblGenderDesc, that held the descriptions. This had two columns, and again two rows, containing the texts "F", "Female" and "M", "Male". I didn't bother trying to review the rest of the database structure, I just declined to take on the assignment. Life is (much) too short for that nonsense.
-
Your data should go through at least the first three stages of normalisation: 1NF, 2NF & 3NF; Boyce-Codd NF is PITA! https://en.wikipedia.org/wiki/Database_normalization[^] Think about, say, Amazon. Amazon have many customers and all these customers can buy many things: an M-M relationship. To get around this, normalisation would result in a third table being created of say 'ORDERS'. So, when you order online, you become ONE customer with your order which can be for many ITEMS. This will then be an M-1-M relationship; one customer can have MANY orders, and one order can have MANY items thereby removing the M-M relationship between customers and items. The same logic can be applied in your M-M case. The hardest part is deciding on what that third table is especially if it isn't immediately obvious. Proper database design can really be a PITA. I had to do a customer database for my gaffer at work to model which machines the customer bought, when they've had them serviced and what spare parts have been used. I think I was at the DB design stage at least a week before I could correctly model the data using normalisation. BTW, I'm not a software or database designer by trade; I'm an engineer who codes for a hobby and studied DB design at college to HNC level.
I did a degree specialising in database design - the upshot of it, when it came to actual work, was this: Follow the rules to the letter with database design then break them all in order to have a database that works both logically and practically :laugh:
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I saw a system last year that had a table, tblGender, to hold the valid values for gender. There was a single column. There were two rows, containing the texts "F" and "M". There was a second table, tblGenderDesc, that held the descriptions. This had two columns, and again two rows, containing the texts "F", "Female" and "M", "Male". I didn't bother trying to review the rest of the database structure, I just declined to take on the assignment. Life is (much) too short for that nonsense.
I can see one possible case where this may needed. I have worked with Spanish systems and the letter used to represent female is "M" in Spanish databases(and "V" for male as "H" is too close to "M" and can confuse people when reading printouts or screens).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I did a degree specialising in database design - the upshot of it, when it came to actual work, was this: Follow the rules to the letter with database design then break them all in order to have a database that works both logically and practically :laugh:
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I can see one possible case where this may needed. I have worked with Spanish systems and the letter used to represent female is "M" in Spanish databases(and "V" for male as "H" is too close to "M" and can confuse people when reading printouts or screens).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Regardless of the values used, there's absolutely no point in having a table with a single column, when that column is also a key in a lookup table of descriptions. I agree there *may* be a case in some situations for having ONE table for gender encodings/descriptions, but never TWO. In this case the system is 100% UK/English based and ALWAYS will be and putting this encoding into the d/b just uselessly degrades application performance for no maintenance or readability benefit.