Number of Database Tables
-
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?
There are two possible reasons for this: 1. You've got a lot better at it. 2. You've got a lot worse at it. If 2, don't let the door hit your @rse too hard on the way out. If 1, don't expect any positive feedback, compliments, or bonuses.
I wanna be a eunuchs developer! Pass me a bread knife!
-
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')
I half agree here, but the general look-up table works for a lot of cases; in other cases another table can be used.
Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
-
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?
-
I half agree here, but the general look-up table works for a lot of cases; in other cases another table can be used.
Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
True - IRL I use a mixture of both with a generic class used to provide binding data for my combos for the general cases. I also tend to go overboard and add start and end dates to them so items can be omitted from the drop down but still exist in the db for historical reasons.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
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'd simply store that in a user-configuration file in the user's profile.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
MehGerbil wrote:
1: I've a tendency to use many to many relationships more often.
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?
MehGerbil wrote:
2: I've a tendency to 'future proof' my applications by adding tables that allow for growth.
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 can understand it if you know functionality is coming but isn't present in the current application releases I guess.
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.
I've always done that so I guess I can see why your table count is increasing!
MehGerbil wrote:
4: I've log tables for everything a user may touch - before and after snapshots of everything.
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! Another alternate I've used is storing old versions of rows in the same table (with an 'End date') so the 'live' record is the one with a null end Date. works well in scenarios where the user might want to view previous versions, as the same GUI can be used. Not so good for very large tables or very frequently changing tables. Another way of keeping it clean is t have the log tables in a different schema - or even a different database.
MehGerbil wrote:
5: I've significantly more items that are configurable.
Amen to that - seems nobody is happy unless they can change the grid settings to their own desires, and save 'em, so their screen looks the same from wherever they log in! Then there's the "oh, well that's not how we run our business!" brigade. (Gone, it seems, are the days of companies being willing to listen to reason and modify their processes to be more efficient - now they just want to computerise the crap they currently have!
MehGerbil wrote:
have you no
_Maxxx_ wrote:
When I started SQL was little more than a glint in its Father's eye
Wow, you must be real old then... SQL/History (wiki)[^]
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
_Maxxx_ wrote:
When I started SQL was little more than a glint in its Father's eye
Wow, you must be real old then... SQL/History (wiki)[^]
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
Rob Grainger wrote:
Wow, you must be real old then...
Yes. Yes I am.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
MehGerbil wrote:
1: I've a tendency to use many to many relationships more often.
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?
MehGerbil wrote:
2: I've a tendency to 'future proof' my applications by adding tables that allow for growth.
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 can understand it if you know functionality is coming but isn't present in the current application releases I guess.
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.
I've always done that so I guess I can see why your table count is increasing!
MehGerbil wrote:
4: I've log tables for everything a user may touch - before and after snapshots of everything.
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! Another alternate I've used is storing old versions of rows in the same table (with an 'End date') so the 'live' record is the one with a null end Date. works well in scenarios where the user might want to view previous versions, as the same GUI can be used. Not so good for very large tables or very frequently changing tables. Another way of keeping it clean is t have the log tables in a different schema - or even a different database.
MehGerbil wrote:
5: I've significantly more items that are configurable.
Amen to that - seems nobody is happy unless they can change the grid settings to their own desires, and save 'em, so their screen looks the same from wherever they log in! Then there's the "oh, well that's not how we run our business!" brigade. (Gone, it seems, are the days of companies being willing to listen to reason and modify their processes to be more efficient - now they just want to computerise the crap they currently have!
MehGerbil wrote:
have you no
_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.
-
In your 10 years of development, have you also noticed the size of your Lists-Of-Reasons change?
Never underestimate the difference U can make in the lives of others.
∫(Edo)dx = Tzumer ∑k(this.Kid)k = this.♥
-
There are two possible reasons for this: 1. You've got a lot better at it. 2. You've got a lot worse at it. If 2, don't let the door hit your @rse too hard on the way out. If 1, don't expect any positive feedback, compliments, or bonuses.
I wanna be a eunuchs developer! Pass me a bread knife!
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.
-
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?
Ahem, the many to many relationships should come from the business requirements, not from your whims. BTW, what is a discrete table? If it means separate tables, then this is a really quite a novel approach to software design, paralleled only by the entity framework invention.
-
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?
I'm just hitting 5 and have been trying to prevent myself from doing it. Particularly since my design for our internal sales system is starting to appear to be a triumph of over engineering. But it'd have to be to manage all of the different things people want it to do. Not sure it's a matter of better or worse, but when you've got people asking for all the functionality in the world now that will probably ask for what happened and who did it when someone screws up later I'm not seeing much of a choice. Or you could drop 1, 4 and 5 and give exactly what's asked for and suffer the consequences of their decisions later. But a question, when it comes to snap shot tables, do you have a unique table tracking full details for each item you want a snapshot of or do you do some more complex mechanism? I've been batting this around for the last few days not being particularly happy with doubling my table count or using a mechanism that'd take a pivot to be able to display a single snapshot row.
-
I half agree here, but the general look-up table works for a lot of cases; in other cases another table can be used.
Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
If you're not careful, you'll next have a lookup types table, so you can organize the lookup data into categories for building things like pulldowns, etc. Works OK if all you ever need are generic things like name/value/is active. Once you go to anything that's specific to that type of data, you should implement a type-specific table, as Nagy alludes to. However, don't forget this ruins data integrity. If you have a bunch of unrelated things in a lookup table and FK to that lookup, all you're guaranteeing is that you'll have some lookup value in your FK field. So, the CarMakeID field which is an FK to lookup might point to a make. It might point to a lookup record representing a US state, or whatever other lookup values you have in there. For that reason, I recommend a lookup sort of table for each type of entity.
-
Yes, much more "Configuration" kind of tables: one called "Genders": Mr., Miss (with possibilty to increase). For the logs I prefer use tracing tools.
The signature is in building process.. Please wait...
-
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?
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?
I prefer to maintain settings outside the main database, preferably into XML files, having said that, i always try to approach database design with a minimalist mindset, so I only have the tables I absolutely need.
CEO at: - Rafaga Systems - Para Facturas - Modern Components for the moment...
-
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.