|
It came up again today how to handle allowing users to add custom fields to the standard entity schemas delivered in your product's database. I favour actually providing a function that gives the user limited DDL functions, so they can actually add a new, custom field to a table. Another approach is to have a separate table for custom fields, e.g. Customers, and CustomersEx, where only CustomersEx can change, but here updates become trickier than normal. The last and most badass option that we discussed was providing a EAV table, where rows are entity name, field name, field value.
Which approach is best?
|
|
|
|
|
EAVs look like a good idea but the practicalities of use (no datatyping, hard to query etc) mean that they should be avoided at all costs.
I have only ever seen this problem handled reasonably well once. They created an Entity table and an attributes table for meta data. They then generated the system and custom tables for each entity from the meta data. They then generated views for querying and stored procedures for insert, update and delete.
Views and SPs were regenerated as needed when custom attributes were added or removed. The other reason for having custom tables for each entity is that there is a limit of 8k on the total row size in Sql Server (at least prior to 2008).
|
|
|
|
|
We are currently using the EAV (what is that by the way) method with an attribute table with AttributeType and AttributeValue. Always varchar of course so yes data typing is an issue. It has allowed us to create a dynamic, extensible solution but there is a definite cost, some of the queries are just horrendous.
If I was going to go down the path RJ suggested I would have a customer table with fixed columns and a CustomerEx where the user can add custom columns and the system will rewrite the DDL. I would disallow any custom column from being in the core processing, IE reference only. I'm not sure if this is different from RJs proposal.
[edit] IJ not RJ, sorry mate [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have problem, i have designed a web page in asp.net 3.5. I have a TextBox and a button, the User is expected to write some comment in the TextBox. The TextBox can also have a comment pattern including a (') on the click of button the data in the TextBox will be inserted into SQL.
Now the problem is : as you know that if u insert something in Varchar it has to be enclosed in ('), but in above case due to (') in the string itself there will be an error
So, is there a way to insert the data in SQL.
Thanks
Pankaj
|
|
|
|
|
Pankaj18 wrote: So, is there a way to insert the data in SQL.
Yup, use parameterized query's;
string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
+ "WHERE CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
command.Parameters.AddWithValue("@demographics", demoXml);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} (Sample taken from MSDN[^])
I are Troll
|
|
|
|
|
Damn, if only I could type faster, I would have got there first.
|
|
|
|
|
Next time you'll be the first to hit the submit-button, it's a small gambling machine.
Anyway, I like it when a post gets (roughly) the same answer multiple times. Always a good sign if more than two developers agree on a solution
I are Troll
|
|
|
|
|
|
If you're not familiar with SQL Injection, which is a security issue, have a read about it. As others suggest you should always parameterize stuff like this rather than construct a string to do it, otherwise you might find your database missing one day...
Regards,
Rob Philpott.
|
|
|
|
|
I am running a stored procedure through a website and is working fine. When I right click on it an select execute to execute it the stored procedure starts executing and after a while the SSMS returns an error message Error message is: Exception of type 'System.OutOfMemoryException' was thrown. What can be the cause of this error message thrown? Thanks
|
|
|
|
|
How many space is there left on your harddisk? Did you check whether you have enough Virtual Memory left?
I are Troll
|
|
|
|
|
Good Day All
i have a Function in my app that is doing a lot of data Validation and Manipulation, This is normally allowed to take longer than 5 minutes because of amount of data. The sp's are optimized and they perform well. But lately we had a new client that has a high demand. One of the table had a million records. I thought of using SSIS to perform this function and execute the package from the C# code.
Questions:
As SSIS is good in Performance in such Situation, Do you think its a good idea to take that Route ?
What can you Suggest and thinks to put in mind when i will have to add SSIS to my ASP.NET Project ?
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I have the code below which works fine in the two WHEN parts. However when the two parts fail, because the group key is absent (a valid condition) I want to choose both, hence the ELSE part. But no rows are returned in this case.
mantype can be W or R
SELECT * FROM callsigns WHERE mantype IN (SELECT CASE
WHEN EXISTS(SELECT 1
FROM group gr1
WHERE gr1.group_key = 'RA'
AND gr1.gra_valid = 1
AND 0 = (gr1.gra_flags & 7)^3)
THEN 'R'
WHEN EXISTS(SELECT 1
FROM group gr2
WHERE gr2.group_key = 'RA'
AND gr2.gra_valid = 1
AND 0 = (gr2.gra_flags & 7)^1)
THEN 'W'
ELSE 'W'+','+'R'
END)
|
|
|
|
|
Try
ELSE SELECT 'W' UNION SELECT 'R'
|
|
|
|
|
I have tried that but get a syntax error
line 14
Incorrect syntax near the keyword 'SELECT'
line 15
Incorrect syntax near ')'
line 14 ELSE SELECT 'W' UNION SELECT 'R'
line 15 END)
I have tried just the SELECT CASE part of the Query with no group key and line 14 as
ELSE 'W'+','+'R' and this returns W,R.
Even tried doubling up the quotes viz: ELSE '''W'''+','+'''R''' which returns 'W','R'
but this also fails to bring back rows
|
|
|
|
|
Ive tried this a few different ways, and hit upon the same error as you've gotten.
I think you're going to be reduced to splitting it into IF..THEN logic
IF EXISTS(SELECT 1
FROM group gr1
WHERE gr1.group_key = 'RA'
AND gr1.gra_valid = 1
AND 0 = (gr1.gra_flags & 7)^3)
SELECT * FROM callsigns WHERE mantype = 'R'
ELSE IF EXISTS(SELECT 1
FROM group gr2
WHERE gr2.group_key = 'RA'
AND gr2.gra_valid = 1
AND 0 = (gr2.gra_flags & 7)^1)
SELECT * FROM callsigns WHERE mantype = 'W'
ELSE
SELECT * FROM callsigns WHERE mantype IN ('W','R')
Edit: I actually think thats also miles more readable than your original too.
|
|
|
|
|
Thanks for that.
I had come to the same conclusion myself. Seems a pity that CASE could not be used a quirk of SQL Server!!
|
|
|
|
|
Check this series of funny (comic style) db4o blog posts which make a great way to get you started with db4o (why not having some fun while you're learning?). The blog posts also deal with key issues that you must have in mind if you're new to db4o (activation, object identity, etc).
Includes:
db4o, The Basics
db4o: Activation- & Update-Depth
db4o: Object-Identity and High-Level-Caching
db4o: Transactions
db4o: Persistent Classes Tips
db4o: Single Object-Container Concurrency
http://developer.db4o.com/blogs/community/archive/2009/10/21/new-to-db4o-learn-have-fun.aspx[^]
|
|
|
|
|
The only reason this has not been voted off as abuse of the forum is because it is open source (note the down votes), if it was commercial it would no longer be here.
If you want to promote something like this why not write an article, there is a product article category specifically set up for this and I'd be surprised if there is a charge for publishing and article on open source stuff.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Gosh im getting a bit sick of writing sql queries for people, maybe im just getting grouchy but this is seriously about the easiest query you could ask for.
Why dont you give it a try, post what you get and what problems you encounter, and i'll be happy to help.
If you're incapable, I suggest you go back to your boss, hand him your keycard and tell him you are not qualified to be a developer.
|
|
|
|
|
J4amieC wrote: Why dont you give it a try,
I hope it does not turns out as one of the recent threads in the visual basic forum.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
'''I tried this following Query but it giving me wrong total what i am missing in following query
SELECT Prd.Item_Name, sum(P.Piece_No) AS PStock, sum(D.Itm_Qty) AS SoldStock, sum(R.Itm_Qty) as RefStock, sum(P.Piece_No) - sum(D.Itm_Qty) - sum(R.Itm_Qty) AS Avl_Stock
FROM Product AS Prd, Purchase_Detail AS P, Detail_Description AS D, Refund_Detail as R
WHERE Prd.Item_Name=P.Item
GROUP BY Prd.Item_Name;
|
|
|
|
|
You need to add links in the WHERE clause for D and R to Prd. At the moment you have cross joins to these two tables so you will get incorrect results.
You may want to look at using LEFT OUTER JOINS as it is possible that products may not have been sold or refunded and in that case, the results will not appear for that product if you use INNER JOINS.
|
|
|
|
|
Thanks for reply i.j.russell
if i am using this
WHERE Prd.Item_Name = P.Item and Prd.Item_Name = d.Item and Prd.Item_Name = R.Item_Name
Its just giving me Refunded items with wrong total,
|
|
|
|