Generic Database Design

Hello,

Can someone please guide me to online resources or books on how to design generic database? I mean, I can have a "Record" that can have a set of fixed fields like: ID, Title, CreatedOn, etc ... and I can add as many properties I want to that record in a vertical way.

Then based on the generic tables present, I can fit in the same table, a Record for Customer, a Record for House, a Record for Order. All share the same set of Fields, but each has its own set of proeprties.

This way, I design my database once, and customize it to fit any type of applications I need.

Is there something like that or just dreaming?

thanks

One way you can go about doing this is build a tool to generate the necessary sql to create the database tables. You can even have it create the stored procedures and code behind to access it. Im not sure if there are tools out there for this, I just wrote my own.|||

Thanks for the reply!

Maybe I was misunderstood. I am asking about having a database design that fits all kinds of applications. I mean design the database once and use it for all my applications. I talked about notion of Record that can hold any object!!

Any more ideas?

Thanks

|||

Perhaps you are talking about the model database in SQL Server. The model database in SQL Server is a template database which can be generalized so that every database created afterwards includes some pre-generated objects. Below are some of the links that you may find useful.

http://articles.techrepublic.com.com/5100-9592_11-6126240.html

http://www.sswug.org/see/30280

Explain your problem in details so that we can help you better.

Hope this will help.

|||

Hello,

Thanks for the reply.

My point is that I want to design a database schema that can fit any application. I mentioned about Record table. This table can hold information about any object regardless of its nature. All objects share a set of predefined fields. I can also assign to each object a number of properties that might be defined somewhere else also in another data table.

My idea is to have a single Database Design that I can use to make it fit for any application. for instance, if I have an application to handle customers and orders, I would define a Record for each object.

Record for Customer

Record for Order

In the database I don't need to do anything, but define two record instances. one for Customer and one for Order. Add any proeprties to each. On the other side, I would develop an API that deals with Records only, regardless if this record is Customer or Order. Each record would have a set of predefined fields and a collection of properties.

Am I making any sense?

Thanks

|||

Its possible I still dont understand you, but how many columns (properties) could every record possible share? Like theres an ID but other than that you are not going to need the address and phone number for Order, but you will for Customer. Im not sure what you hope to accomplish by doing this. Do you want to be able to programmitcally add new columns to a table?

Or are you talking about assuming that you will need an Order table and a Customer table in every project you work on, and you want to create almost a template that you can modify to fit the needs of some specific project?

|||

Hello,

What I am thinking of is to have a notion of Record. A customer is stored in one record and an order is stored in one record, a student is stored in one record, etc ... This includes only basic fields.

Also, I want another table call it Property, that holds additional proeprties of each record. By using properties I can customize my database to configure any new website's objects with the propeties required. So you can think of having one framework for the database design that can fit any project.

Does it make any sense?

Thanks

sql