Number of Database Tables
-
We've a vended application where every textbox/label on all of the forms have their positions/size stored in the database because they've had many users ask to move this here or that over there.
Sounds like BAAN(a colleague explained how badly one this was in BAAN) - I don't do this with everything but only those parts of the UI which I know the users are going to want a quick turnaround on. It's a pattern that allows me to concentrate of getting the users what they want fairly quickly in which the controls will display everything based on the tables at the back end rather than some hidden business logic in the exe. [edit typo - login changed to logic]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Why use the data layer to configure the application? What if you need config for the database, then you'll need a database to store the data storage config. And where is that config stored?
Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
Yes if you're not careful it can become a never ending recursive hole - it is to be used judiciously. Just from personal experience I have found meta tables to be very helpful, in the end it is just a preference and I am open to doing things differently if there is a better and more effective way for the situation I am working with.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Yes if you're not careful it can become a never ending recursive hole - it is to be used judiciously. Just from personal experience I have found meta tables to be very helpful, in the end it is just a preference and I am open to doing things differently if there is a better and more effective way for the situation I am working with.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
As I've got biggerer and cleverer I've reverted a lot, moving away from db's as they often cause unneeded complexity. "Kevin Is Still Suave" as they say in Melton Mowbray.
Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
-
0. Data is stored off DB as documents. ;P As an aside, why not a single kitchen sink look up?
SELECT CODE, DESCRIPTION FROM LOOK_UP WHERE TYPE = 'FOO_BAR'?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?
Worked with an application about 25-30 years ago, an Information Engineering CASE tool, if anyone remembers those. This thing was used for static business modeling, business systems analysis (static, also), had some design capabilities. My guess is there are 100 different types of things in there...and they did it all with 4 tables: one each for Object (please, I know it wasn't an OO Object), Attribute (of the object), Relationship (between objects), and a fourth just for handling long text. As I remember, each had about 4 fields. BTW, I too find that when designing the tables for my apps there is a tendency on my part to over engineer for the what-ifs of the world. That's OK, because each time I learn another way to git 'er dun!
cat fud heer
-
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?
Current website I work on has about 700 (in the primary database), but the website contains many "applications". I'm sure my last job must have had thousands (or maybe it was just tons of stored procedures). When I started, I created very few tables. Though, I still create very few (mostly just use existing ones). And when I create them, I try to make them generic enough that they can be used in a bunch of places.
-
Sounds like BAAN(a colleague explained how badly one this was in BAAN) - I don't do this with everything but only those parts of the UI which I know the users are going to want a quick turnaround on. It's a pattern that allows me to concentrate of getting the users what they want fairly quickly in which the controls will display everything based on the tables at the back end rather than some hidden business logic in the exe. [edit typo - login changed to logic]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Thankfully I no longer need to cater for that sort of vagary, with a small audience for each app they get what they want in the UI and if they want it changed then they get a sign off on it from the other users. Man have I seen some bun fights over the positioning of a button, it usually stays where I put it.
Never underestimate the power of human stupidity RAH
-
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 think it is called maturity, when you are still wet behind the ears you don't have the experience and knowledge to envisage the deeper requirements of a solution. Part of my job is to sit in on some of the young ones starting design, fascinating to see the light go on when you explain why you need a dimension table for something. So many of then think there are only 2 genders or countries never change their name or currencies.
Never underestimate the power of human stupidity RAH
-
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?
My largest data base app has four tables. The smallest has one. Needless to say, I'm not really a data base developer. I just play one at work on occasion.
Software Zen:
delete this; -
Why use the data layer to configure the application? What if you need config for the database, then you'll need a database to store the data storage config. And where is that config stored?
Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
I worked on a system a couple of years ago that had pretty much that scenario. A config file installed with the application specified the URI of a database. IN that database was solely security and config data - which pointed the application to the data database. Was quite nice; The data server hosting could be changed for all users and they would be updated when connecting to the security & config database without having to change anything on the client machines.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
0. Data is stored off DB as documents. ;P As an aside, why not a single kitchen sink look up?
SELECT CODE, DESCRIPTION FROM LOOK_UP WHERE TYPE = 'FOO_BAR'?Reality is an illusion caused by a lack of alcohol "Nagy, you have won the internets." - Keith Barrow
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')
-
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've observed an increase in complexity of all software with little comparable increase in capability. My own theory is that tool and framework complexity encourages, and sometimes requires, developers to make more complex designs.
-
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:
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
-
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?
-
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.