views:

236

answers:

11

Hello, How do people make reusable databases that can be used for many products?

For example,if we have a database designed for a school...Can it be easily modified to be given to a college?

What is the way to create a database that can be used as a product to give solution to many customers with coding just once?

Thanks

+2  A: 

There's no simple silver bullet for this. You just need to keep your database design general enough, but try to avoid over-generalization as that usually leads to nightmares in maintenance and other nasty pitfalls.

With experience, you'll start to appreciate a perfect balance between generalized and specialized. That's the key to understandable and reusable code / database design.

Tommi Forsström
It could be true, if application in same domain, in that case, application act like a product for every one...
Syed Tayyab Ali
A: 

the best advice I can give is to build to the lowest common denominator....

So....Code it as a project geared towards education facilities :-)

Ian Jacobs
A: 

Spend time thinking about the type of data you want to store, abstract it in order to make it extendable, and then build your database accordingly. I don't know if you can get perfect reuse the way you might from code, but you can build a database structure (where you still have to modify individual components) that is reusable if you plan ahead.

Elie
+1  A: 

The answer is to find a sweet spot of abstraction.

Ed Guiness
lol @ linking 'sweet spot'
Mark
For ESL folk for who might find the term odd
Ed Guiness
A: 

It depends on your needs. For instance, many many databases for product based business use a format that includes:

  • a customer table
  • an orders table
  • an products table, etc

In your situation you might have

  • a class table
  • a students table
  • a grades table, etc.

This general table format could be reused in many applications.

CLaRGe
A: 

It's all in the design. In many (if not most) cases, databases will need some level of customization for individual institutions; but generalized databases can provide a basic level of functionality. It IS possible to design something that is general enough to serve many basic needs; but the problem is that that generality of design tends to lead to high complexity. For example, you CAN design your database to be data-driven for a large set of potential user needs; but usually, it's just better to customize the schema to the individual needs of the institution.

There are significant tradeoffs involved in designing for a reusability situation; usually they involve time and complexity; i.e., it's easier to design something that is NOT reusable; and usually, the additional amount of time invovled in making the general design and using it is not worth the effort.

McWafflestix
A: 

Find the right balance of generality and specificity in your database design so that the application you build around it solves enough problems in your targeted markets that they'll all buy into it.

John at CashCommons
+3  A: 

Usually, when people do this, they have multiple clients in the same industry. So if you are an ecommerce web developer, then you are going to run across the same products, order, order details type of table scenario over and over again. When this happens, it's a breeze to build a starter database.

John MacIntyre
And if you're going to go this route, you should also maintain upgrade scripts between each new version that you make so you can more easily go back and upgrade more clients.
Brent Ozar
A: 

An application such as this would require a fairly complex data model to account for the requirements. Different kinds of schools would have different requirements. A university might let you add or drop courses, but an elementary school typically does not. A university needs to schedule courses into rooms, whereas an elementary school needs to put students into grades and split grades into classrooms based on available space and teachers.

Your design needs to take into consideration all of the requirements it is expected to solve and then implement those. The more generic you make the program the harder it is to satisfy your customers. The question says "write the code once". If you want to write a single program that solves every school's needs, it will need hundreds of features; in some cases some schools will require an opposite feature from another school; for example some schools will need to enforce one teacher per subject or classroom while another school might require multiple teachers. The more requirements you expect to meet, the more complex the application becomes.

In industry big applications tend to be written so that they can be extended; a core set of functionality is provided but the application is meant to be changed and customized for a particular customer. This makes it easier to develop because you don't need to anticipate every need; in fact you won't need to anticipate many needs until you have a customer with those needs. But with "customization" you are not writing code only once.

The most important step is to come up with a data model that is flexible enough to extend later on, but isn't so flexible that it is impossible to develop for. The hardest part is usually getting the cardinality of relationships correct. For example, you might say a class has one teacher. Then when it turns out a class needs two teachers, you have to rewrite a lot of code and fix up a lot of data. These kinds of changes are annoying and time-consuming. However, in the end you can always fix mistakes given enough programmer time.

Mr. Shiny and New
A: 

Will each customer use all of the functionality or are you trying to build a a one size fits all product? I've always found that additional time spent planning and modifying a database to suit a particular application pays off in the future. It's much easier to work with a concise database structure than one where you have tried to account for every possibility.

If I have an existing database that is similar or a template I usually use a database modeling tool like this to modify it and then use the generate SQL functionality (under load/save) to create the actual database.

Another trick I picked up recently that has saved me a lot of time is to save the SQL used to generate the database as a script. If I want to set up a new database I make any edits to the source code, and then load the page. For example, if I wanted to generate a new customer table I load http://localhost/load.php?generate=customer.

Hope it helps!

Matt
+1  A: 

Step one, talk to a very wide variety of the potential clients and find out their needs, what they are currently using and what they wish their current product(s) could do. Spend 10 times as long at this as you think you need to right now. Draw out a potential GUIs on paper and have the people you interview look at the drawings and make suggestions. If at all possible, hire some people in the industry as business analysts to help with this step. Ask about legal requirements. Some industries have a lot of legal complicance issues and others do not. Anything related in any way to the medical world and you will need to research and fully understand HIPPA requirements, for instance.

Design the database structure and a GUI then get some real users to play with it. Refactor based on what they say (it's amazing how many things users leave out in requirements gathering that they don't think of until faced with an actual GUI).

Think about what needs to common through all the potential customers and where you might need customization - your interviews should guide you here. Decide how to handle customization. Or even if you will allow it. This may depend a great deal on the industry and how standard their practices are.

If this is box software, often the design includes a table with customizable fields that can be added to forms and reports by the user.

In a web-based solution, often each user wanting customization may have their own database where the custom information is stored (and a central standrad database for the noncustomizable things) and the programmers make the changes based on requests from the clients. If you take this route, the second time you do a simliar customization for a second client, consider if you need to refactor to make this a new feature of the software available to everyone. No need to write 17 custom attendance reports that vary only by one or two fields when the client can for less money have a standard report.

In the web model, you can also create a bunch of modules and have the clients pick and choose which to add to their custom solution. They would pay based on the number and complexity of the modules they choose. So the client that wants only three of the standard reports would pay less than the client that wants all 27. When a new customization is suggested, the client pays for development if the suggestion doesn't seem to immdeiately apply to others, but the module is done so that others can buy it as well. If others buy it, the orginal client who asked for the change might get part of the money until their development costs is paid. They could also require that something remain as a custom module and pay a much higher price for this work. We have some clients who don't even want their data on the same servers in the same location as other customers. Needless to say, we charge a huge premium for doing something like that.

Customization is expensive and can lead to many more programmers needed. Consider very strongly before you go the customization route. It can really be the thing which sells your software solution, but it doesn't scale well. It isn't bad when you have ten cutomers but when you have a couple of hundred it can get out of control very quickly. It is a lot harder to back off of customization once you offer it, than to add customization later from a standard suite. Often the need for customization is more in the organization of corporate reporting. If you can create a reporting interface where people can pick and choose what information they want and save their own custom reports, you might handle most of the customization needs in your industry without full-scale customization needed.

HLGEM