Hi,
I know this is probably not the right place to post because my problem is more an ER design problem than an OO design problem.
However, I hope you'll be able to give me some clues.
I have datas that have to be stored in a relational database (MaxDB). I have to design a database schema to store these datas, but I have too make possible to add fields in tables as the data provider can add fields without telling us. We also don't want to have to alter the database schema (the database user will probably won't have the right to do it).
So I thought that I could use an entity to store fields and add a relationship between the main application entity and this field entity, enabling the application entity to be linked to 0..n fields.
Below is the ER model with the two entities resulting of these design.
This would work in the case that ALL fields have the same data type. So I should say for example that the Value field is a char(20).
All would be easy.
But this is not the case. Each field have a different type and when having the same type, a different length.
So I can have INTEGER, VARCHAR(20), VARCHAR(10), CHAR(2), BIT, DOUBLE, DATE, etc...
I then designed the thing in a different way :
Note that I'm aware that my model is not anymore in a normal form. That's one of my problem because I feel it will create problems when we'll try to make requests on this database. I'll have to create SQL requests dynamically :
In this request, I have to build a list of tables to select from. So I have to inspect database meta datas and build a list, then use it in my requests.
I have to run through all types tables to find a field.
So I added a fieldType entity and a relation with the field entity to indicate the type of the field in order to speed the retrieval of the field value.
But perhaps it's not so useful as I don't know if the previous design would be so slow... As it's an history database, it could contain a lot of datas...
I think this design is better that having a lot of fields in a table with most of them containing NULL values and having to alter the table each time we have a new field. But I wonder if it's really a good way to design this. I searched for a pattern aimed to solve this problem, but without success.
So how would you have approached this problem ? Do you know some design patterns, some experiences ?
Pros and cons are really welcome.
Thanks,
Nicolas.
Geeks are people who thinks that a kilometer is 1024 meters long.
I know this is probably not the right place to post because my problem is more an ER design problem than an OO design problem.
However, I hope you'll be able to give me some clues.
I have datas that have to be stored in a relational database (MaxDB). I have to design a database schema to store these datas, but I have too make possible to add fields in tables as the data provider can add fields without telling us. We also don't want to have to alter the database schema (the database user will probably won't have the right to do it).
So I thought that I could use an entity to store fields and add a relationship between the main application entity and this field entity, enabling the application entity to be linked to 0..n fields.
Below is the ER model with the two entities resulting of these design.
This would work in the case that ALL fields have the same data type. So I should say for example that the Value field is a char(20).
All would be easy.
But this is not the case. Each field have a different type and when having the same type, a different length.
So I can have INTEGER, VARCHAR(20), VARCHAR(10), CHAR(2), BIT, DOUBLE, DATE, etc...
I then designed the thing in a different way :
Note that I'm aware that my model is not anymore in a normal form. That's one of my problem because I feel it will create problems when we'll try to make requests on this database. I'll have to create SQL requests dynamically :
Code:
SELECT Value FROM Int10,VarChar20,DateField WHERE FieldFieldID = 12I have to run through all types tables to find a field.
So I added a fieldType entity and a relation with the field entity to indicate the type of the field in order to speed the retrieval of the field value.
But perhaps it's not so useful as I don't know if the previous design would be so slow... As it's an history database, it could contain a lot of datas...
I think this design is better that having a lot of fields in a table with most of them containing NULL values and having to alter the table each time we have a new field. But I wonder if it's really a good way to design this. I searched for a pattern aimed to solve this problem, but without success.
So how would you have approached this problem ? Do you know some design patterns, some experiences ?
Pros and cons are really welcome.
Thanks,
Nicolas.
Geeks are people who thinks that a kilometer is 1024 meters long.