Need database advice

Interesting issue you have there...

I think I would go with either.

1. Dynamically reacreate table and insert required columns..

2. Use a retrieve and store function to split and combine the date into one field per customer. (Deliminated method)

As I see it both have pros and cons.

1. This will be a easier to to retrieve and display.. searches indexing exct however re createing thr table will require a rather large script allong with the added issues of Posibly confusing datasets and so on.

2. This will most definitely be the quickest.. get the entire field per record and split for viewing. But that would mean indexing and filtering delays.. only however if your using the db itself.. if you allready have the pk value of the record.. it wont be an issue...

Just my thoughts.. :p

I might even be barking up the wrong tree.
 
Just be really careful with performance when using data types which are not standard SQL types. You really need to know what you are doing with the indexes if they are going be in the where clause.

The solution is often also not ANSI standard but vendor specific. Be aware of that too.

And document it properly :) The whole thing can be undone later on, because it had to be updated in a rush and you forgot what you did. :)
 
Last edited:
You should really consider making stack overflow your first port of call. http://stackoverflow.com/questions/...ns-to-support-custom-fields-in-an-application

For simplicity, I like option 4 with an extra field for <DataType>, and maybe an optional <Required> one -- to ascertain if UI should require data entry.

As they mentioned; it's best to not to use a single VARCHAR field for all datatypes; you'll only end with a lot of unnecessary validation / casting code, and that's not even considering the lack of meaningful indexing or the potential for value / datatypes bugs.
 
Last edited:
[)roi(];11653759 said:
You should really consider making stack overflow your first port of call. http://stackoverflow.com/questions/...ns-to-support-custom-fields-in-an-application

For simplicity, I like option 4 with an extra field for <DataType>, and maybe an optional <Required> one -- to ascertain if UI should require data entry.

As they mentioned; it's best to not to use a single VARCHAR field for all datatypes; you'll only end with a lot of unnecessary validation / casting code, and that's not even considering the lack of meaningful indexing or the potential for value / datatypes bugs.
Click to expand...

Stackoverflow is my saviour 90% of the time. :D

I haven't progressed any further though, the separate table method I tried didn't have the desired performance increase I hoped for. I think the only way to speed up things up would be to 1. dynamically create a new table each time, or 2. move the entire database over to something like MongoDB or CouchDB. Both of these options I don't really want to entertain though, I'll see though.
 
DrJohnZoidberg said:
Stackoverflow is my saviour 90% of the time. :D

I haven't progressed any further though, the separate table method I tried didn't have the desired performance increase I hoped for. I think the only way to speed up things up would be to 1. dynamically create a new table each time, or 2. move the entire database over to something like MongoDB or CouchDB. Both of these options I don't really want to entertain though, I'll see though.
Click to expand...
I'd suggest never trying to adapt a data structure to fit a UI; far better to adjust the UI to support a more efficient data structure.

Is there any particular reason for your approach? or any reason to not adjust the UI?
 
Last edited:
Option 4 is even worse than option 1

Having a table with n columns but only one column per row actually has a value should immediately have you rejecting it. A clear Db "anti pattern"
 
_kabal_ said:
Option 4 is even worse than option 1

Having a table with n columns but only one column per row actually has a value should immediately have you rejecting it. A clear Db "anti pattern"
Click to expand...
Huh?
1 column per row? Expand on this iro: achieving design with n custom fields

IMO,

Option 3, requires a casting performance hit, and a potential for incorrect casting bugs.

Option 4 whilst not ideal re null value fields is certainly considerably better than a single varchar field with delimited strings, and/or xml/json. A next step with option 4 could be to consider separate tables for each data type to overcome the null fields.

Another option would be create / recreate a custom table per client, less performance hit but also quite messy re table per client.

Whichever way, implementing custom n dimension fields is by requirement inelegant and so too the solutions.
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X