DB Design: more tables vs less tables

Say I want to design a database for a community site with blogs, photos, forums etc., one way to do this is to single out the concept of a "post", as a blog entry, a blog comment, a photo, a photo comment, a forum post all can be thought as a post. So, I could potentially have one table named Post [PostID, PostType, Title, Body .... ], the PostType will tell what type of post it is.

Or I could design this whole thing with more tables, BlogPost, PhotoPost, ForumPost, and I'll leave Comment just it's own table with a CommentType column.

Or have a Post table for all types of post, but have a separate Comment table.

To be complete I'm using ADO.NET Entity Framework to implement my DAL.

Now the question what are some of the implications if I go with any route described above that will influence on my DB performance and manageability, middle tier design and code cleaness, EF performance etc.?

Thank you very much!

Ray.

From stackoverflow

• Let me ask you this:

  What happens if two years from now you decide to add a 'music post' as a blog type? Do you have to create a new table for MusicPost, and then re-code your application to integrate it? Or would you rather log on to your blog admin panel, add a blog type in a drop-down box called 'Music', and be on your merry way?

  In this case, less tables!

  Kev : Wouldn't you have to add more specific code, e.g., to have a music player object embed in the page? But generally I agree.

  cLFlaVA : Potentially, yes. However, the modification you mention would still be far less work than revisiting the entire database structure.

• The problem is similar to the question of how deep your hierarchy should be in an OO design.

  A simple approach in OO terms would be to have a base class Post and children for BlogPost, ForumPost and so on. Comment could either be a child of Post or its own hierarchy, depending on your requirements.

  Then how this is going to be mapped to DB tables is an entirely different question. This classical essay by Scott Ambler deals with the different mapping strategies and explains their advantages and disadvantages in a rather detailed way.

  Tony Andrews : Essays by Scott Ambler on database design always make me queasy - but I'm not down-voting you for it!

  Joachim Sauer : @Tony: really? is there a background to that feeling? I'd be interested in that.

• Generally, life will be easier if you can have all the posts in one table:

  • less joins to perform
  • less tables to maintain
  • common attributes are not repeated between tables
  • code more generic

  However, you could run into some issues:

  • if each subtype has a lot of its own attributes, you could end up with many columns - maybe too many for your DBMS
  • if a subtype has an attribute (e.g. a stored picture) that is expensive for your DBMS to maintain even when unused, you might not want that column in all rows

  Should you run unto such an issue, you can create a new table just for the specific attributes of that post subtype - for example:

  create table posts (post_id number primary key, 
                      post_date date,
                      post_title ...); /* All the common attributes */
  
  create table photo_post (post_id references posts, photograph ...);
  

  In many cases, no such issues arise and a single table for all will suffice.

  I can't think of any merit in creating a distinct table for every subtype.

Convert String from ASCII to EBCDIC in Java?

Hello,

I need to write a 'simple' util to convert from ASCII to EBCDIC?

The Ascii is coming from Java, Web and going to an AS400. I've had a google around, can't seem to find a easy solution (maybe coz there isn't one :( ). I was hoping for an opensource util or paid for util that has already been written.

Like this maybe?

Converter.convertToAscii(String textFromAS400)
Converter.convertToEBCDIC(String textFromJava)

Thanks,

Scott

From stackoverflow

• It should be fairly simple to write a map for the EBCDIC character set, and one for the ASCII character set, and in each return the character representation of the other. Then just loop over the string to translate, and look up each character in the map and append it to an output string.

  I don't know if there are any converter's publicly available, but it shouldn't take more than an hour or so to write one.

• You can create one yoursef with this translation table.

  But here is a site that has a link to a Java example.

• You should use either the Java character set Cp1047 (Java 5) or Cp500 (JDK 1.3+).

  Use the String constructor: String(byte[] bytes, [int offset, int length,] String enc)

• JTOpen, IBM's open source version of their Java toolbox has a collection of classes to access AS/400 objects, including a FileReader and FileWriter to access native AS400 text files. That may be easier to use then writing your own conversion classes.

  From the JTOpen homepage:

  Here are just a few of the many i5/OS and OS/400 resources you can access using JTOpen:

  • Database -- JDBC (SQL) and record-level access (DDM)
  • Integrated File System
  • Program calls
  • Commands
  • Data queues
  • Data areas
  • Print/spool resources
  • Product and PTF information
  • Jobs and job logs
  • Messages, message queues, message files
  • Users and groups
  • User spaces
  • System values
  • System status

  scottyab : We are using the JTopen tool box and it is doing some of the convertion/mapping, it's just it seems to incorrectly map £,$,[ and ^

  Thorbjørn Ravn Andersen : Sounds like your AS/400 is incorrectly configured regarding its native tongue. If it is set up correctly jt400.jar will not require any other tweaking.

  Mike Wills : Yes, the conversion should happen basically automatically. If it isn't, something isn't setup right.

• Please note that a String in Java holds text in Java's native encoding. When holding an ASCII or EBCDIC "string" in memory, prior to encoding as a String, you'll have it in a byte[].

  ASCII -> Java:   new String(bytes, "ASCII")
  EBCDIC -> Java:  new String(bytes, "Cp1047")
  Java -> ASCII:   string.getBytes("ASCII")
  Java -> EBCDIC:  string.getBytes("Cp1047")
  

  Thorbjørn Ravn Andersen : There are many EBCDIC code tables. It is very tedious to get right manually.

• why would you write it if java already supports this charset?

Javascript Error thrown by AjaxToolKit in .NET only on some machines

We have a button that saves asynchronously using AjaxToolKit/C#/.NET. I'm getting this in my Error Console:

Error: [Exception... "'Sys.WebForms.PageRequestManagerServerErrorException: Sys.WebForms.PageRequestManagerServerErrorException:
An unknown error occurred while processing the request on the server. The status code returned from the server was: 500' when calling method:
[nsIDOMEventListener::handleEvent]"  nsresult: "0x8057001c (NS_ERROR_XPC_JS_THREW_JS_OBJECT)"  location: "<unknown>"  data: no]

The strangest thing about this is that it's only happening on some machines (but not all). Everyone in the office checked this out in FF 3.0.4 and IE 7, and the save works fine for most people. For the handful of people that it doesn't work for, it fails in both browsers.

Any ideas of where to start troubleshooting this?

1. I'd say it was a server/code error, but it works for most people.
2. I'd say it was a javascript error, but we're all using the same browser.
3. I might even say it was an OS difference, but it happens to both XP and Vista users.
4. User Permissions are the same for all of us

The application was working correctly until this weekend's update, where we updated a UserControl on the page. If I remove that control, the save works fine on all computers. When I add the control, we have the above situation where it fails on random computers.

Answer

Unfortunately, this was hard to track down due to the mysteriousness of how AjaxToolKit returns errors. The real culprit was that there was HTML in some of our fields in the UserControl. We added ValidateRequest="false" to the top of our page, and our problems disappeared.

The way we ultimately found the problem was to remove the UpdatePanel from around our Save button.

Sorry I didn't think of this sooner, because it was pretty straightforward from there. I'm giving Michael the accepted answer for his hard work and exhaustive troubleshooting list.

From stackoverflow

• Things to check:

  1. Reboot (oldie but goodie, and doesn't always go without saying)
  2. OS version (you did this)
  3. Web browser (you did this)
  4. Web browser settings (you covered this by trying multiple browsers)
  5. Network connectivity and Hosts file rules
  6. User permissions (you covered this)
  7. Application permissions (you covered this)
  8. OS locale settings (control panel: Regional and Language Options)
  9. Try and capture more information.
     • http://www.communitymx.com/content/article.cfm?cid=A66B8
     • http://forums.asp.net/p/1163435/2673051.aspx
     • http://projects.nikhilk.net/WebDevHelper/Default.aspx
     • http://www.fiddlertool.com/fiddler/
     • IIS logs; Server Event Log

  In this situation, I'd probably download a virgin browser to try to rule out browser settings. I'd throw Google Chrome onto a known good machine and a known bad machine to see what happens. If it works in both cases, then I'd look further at browser settings. I know that this isn't likely the case since it fails in both FF and IE, but IE this simple test could help provide a little more info nonetheless.

  It's a weird one. Since it affects both IE and FF, I'd also look at the connectivity angle, the hosts file, and permissions.

  Jon Smock : Good list - I'll edit my question to clarify some of these

  Jon Smock : Good call on the logs, searching now...

  Jon Smock : Can't find anything useful in IIS logs, Chrome didn't work on bad machine.

  Michael Haren : OK, since Chrome failed, we can ignore all the browser stuff, like cache, permissions, etc. What about the OS and network stuff? I assume you did a reboot, too...?

  Michael Haren : (Note that Chrome has to work on a good machine to be a valid test on the bad machine)

• It is not the error of Ajax, Somewhere your code has error.remove Ajax and then check,u will come to know What is exact error.

  Ajit

Output library project XML to asp.net bin folder on build?

I have a visual studio 2005 solution which has a web application and a class library project. The web application has a reference to the library project. I'd like the library project's code documentation XML to output to the web application's bin folder, along with the library's DLL. I can't seem to find any easy way of doing this.

From stackoverflow

• Post-build step, perhaps? A bit ugly, but I think it would work.

• Use a post build event on the library project that will copy the Xml file to the web application's bin folder.

  For example you could use something like: copy $(TargetDir)\ $(SolutionDir)\

  This is untested so you'll prolly need to tweak it.

• Here is the post-build command that worked:

  copy "$(TargetDir)$(TargetName).xml" "$(SolutionDir)MyWebProject1\bin\$(TargetName).xml"
  copy "$(TargetDir)$(TargetName).xml" "$(SolutionDir)MyWebProject2\bin\$(TargetName).xml"
  

  A couple of problems inherent with this solution:

  • The xml is always copied even if that website is not part of the current build... so the documentation can get out of sync with the the dll in the bin folder until the next time that web project is built.
  • If we add a new web project that refers to this library, we need to add another post-build command, rather than having this all happen automatically.

  I'm accepting this as a kludgy but workable solution... if anyone has a more elegant suggestion, please let me know!

  Thanks,

  Mike

Adobe Air - Using multiple SQLite databases at once

I have 2 SQLite databases, one downloaded from a server (server.db), and one used as storage on the client (client.db). I need to perform various sync queries on the client database, using data from the server database.

For example, I want to delete all rows in the client.db tRole table, and repopulate with all rows in the server.db tRole table.

Another example, I want to delete all rows in the client.db tFile table where the fileID is not in the server.db tFile table.

In SQL Server you can just prefix the table with the name of the database. Is there anyway to do this in SQLite using Adobe Air?

From stackoverflow

• SQLite databases exist independently, so there's not way to do this from the database level.

  You will have to write your own code to do this.

• It's possible to open multiple databases at once in Sqlite, but it's doubtful if can be done when working from Flex/AIR. In the command line client you run ATTACH DATABASE path/to/other.db AS otherDb and then you can refer to tables in that database as otherDb.tableName just as in MySQL or SQL Server.

  Tables in an attached database can be referred to using the syntax database-name.table-name.

  ATTACH DATABASE documentation at sqlite.org

• I just looked at the AIR SQL API, and there's an attach method on SQLConnection it looks exactly what you need.

  I haven't tested this, but according to the documentation it should work:

  var connection : SQLConnection = new SQLConnection();
  
  connection.open(firstDbFile);
  connection.attach(secondDbFile, "otherDb");
  
  var statement : SQLStatement = new SQLStatement();
  
  statement.connection = connection;
  statement.text = "INSERT INTO main.myTable SELECT * FROM otherDb.myTable";
  statement.execute();
  

  There may be errors in that code snipplet, I haven't worked much with the AIR SQL API lately. Notice that the tables of the database opened with open are available using main.tableName, any attached database can be given any name at all (otherDb in the example above).

• the code of floor2 can not use in flex3~~~ who can give me right code?

• this code can be work,it is write of me: package lib.tools { import flash.utils.ByteArray;

  public class getConn
  {
   import flash.data.SQLConnection;
   import flash.data.SQLStatement;
   import flash.data.SQLResult;
   import flash.data.SQLMode; 
   import flash.events.SQLErrorEvent;
   import flash.events.SQLEvent;
   import flash.filesystem.File;
   import mx.core.UIComponent;
   import flash.data.SQLConnection;
  
   public var Conn:SQLConnection;
  

  /* 定义连接函数
  wirten by vivid msn:guanyangchen@126.com */

   public function getConn(database:Array)
   {  
              Conn=new SQLConnection();
              var Key:ByteArray=new ByteArray(); ;
              Key.writeUTFBytes("Some16ByteString"); 
              Conn.addEventListener(SQLErrorEvent.ERROR, createError);
              var dbFile:File =File.applicationDirectory.resolvePath(database[0]);
  
              Conn.open(dbFile);
              if(database.length>1){
                  for(var i:Number=1;i<database.length;i++){
                   var DBname:String=database[i]
                   Conn.attach(DBname.split("\.")[0],File.applicationDirectory.resolvePath(DBname));
                  }
              }
  

  /* 加密码的选项
  wirten by vivid msn:guanyangchen@126.com */

             Conn.open(dbFile, SQLMode.CREATE, false, 1024, Key); 
  
   }
  

  /* 出错返回信息函数
  wirten by vivid msn:guanyangchen@126.com */

      private function createError(event:SQLErrorEvent):void
                      {
                          trace("Error code:", event.error.details);
                          trace("Details:", event.error.message);
                      }
  

  /* 定义执行sql函数
  
  wirten by vivid msn:guanyangchen@126.com */

      public function Rs(sql:Array):Object{
          var stmt:SQLStatement = new SQLStatement();
       Conn.begin();
       stmt.sqlConnection = Conn;
  
  
       try{
           for(var i:String in sql){      
         stmt.text = sql[i]; 
                  stmt.execute();
           }
              Conn.commit();
          }catch (error:SQLErrorEvent){
         createError(error);
         Conn.rollback();
       };
  
          var result:Object =stmt.getResult();
          return result;
      }
  
  
  
  }
  

  }

Flex: Is there anyway to disable the textfield in the NumericStepper and force the user to change the value only by using the up/down buttons?

Probably not much more to elaborate on here - I'm using a NumericStepper control and I want the user to use the buttons only to change the value in the NS, not by typing into the control - I couldn't find a property to disable the text - does it exist?

If it doesn't, how would I subclass this thing to disable the text?

From stackoverflow

• Ok - I think I got it - there is no property you can set but you can subclass the control and set:

  mx_internal::inputField.enabled = false;
  

  Although that sets up next question about what the hell mx_internal is...

• mx_internal is a namespace. There's a good explanation of how it all works here:

  http://nondocs.blogspot.com/2007/04/mxcoremxinternal.html

• In general, if you're using mx_internal, there's a decent chance that your app will break between flex versions.

Detecting multiple logged on users via win32

Using the standard win32 api, what's the best way to detect more than one user is logged on? I have an upgrade to our software product that can't be run when more than one user is logged in. (I know this is something to be avoided because of its annoyance factor, but the product is very complicated. You'll have to trust me when I say there really is no other solution.) Thanks.

From stackoverflow

• In order to have more than one user logged in at once, Terminal Services or Fast User Switching must be enabled. Since Fast User Switching is implemented using Terminal Services, you first need to find out if the OS has it enabled. You can use GetVersionEx with an OSVERSIONINFOEX. Check for the VER_SUITE_SINGLEUSERTS and VER_SUITE_TERMINAL flags.

  If TS is enabled, you can use WTSEnumerateSessions to find out how many users are logged on. This only works if the "Terminal Services" service is started.

  If the machine doesn't support Terminal Services (or if the service isn't started), then you can only have one user logged on.

  Paul Betts : This is incorrect, with fast-user switching, > 1 user could be running processes at the same time.

  Roger Lipscombe : @Paul Betts: If you're using Fast user switching, then WTSEnumerateSessions will return all currently logged-on sessions, whether they're active or not.

• This might be a roundabout way, but run down the process list and see who the process owners are.

• Here's a solution that works on XP, Server 2003, Vista, and Server 2008. Note, this won't work on Windows 2000, because "LsaEnumerateLogonSessions" is not available on Windows 2000. This code is modified from a Delphi-PRAXIS post.

  To compile this, create a new VCL application with a TButton and a TMemo on the form. Then copy and paste this code and it should compile. I tested on XP and Vista and it works well. It will return interactive and remote users.

  unit main;
  
  interface
  
  uses
    Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
    Dialogs, StdCtrls;
  
  const
    WTS_CURRENT_SERVER_HANDLE = 0;
  
  type
    PTOKEN_USER = ^TOKEN_USER;
    _TOKEN_USER = record
      User: TSidAndAttributes;
    end;
    TOKEN_USER = _TOKEN_USER;
  
    USHORT = word;
  
    _LSA_UNICODE_STRING = record
      Length: USHORT;
      MaximumLength: USHORT;
      Buffer: LPWSTR;
    end;
    LSA_UNICODE_STRING = _LSA_UNICODE_STRING;
  
    PLuid = ^LUID;
    _LUID = record
      LowPart: DWORD;
      HighPart: LongInt;
    end;
    LUID = _LUID;
  
    _SECURITY_LOGON_TYPE = (
      seltFiller0, seltFiller1,
      Interactive,
      Network,
      Batch,
      Service,
      Proxy,
      Unlock,
      NetworkCleartext,
      NewCredentials,
      RemoteInteractive,
      CachedInteractive,
      CachedRemoteInteractive);
    SECURITY_LOGON_TYPE = _SECURITY_LOGON_TYPE;
  
    PSECURITY_LOGON_SESSION_DATA = ^SECURITY_LOGON_SESSION_DATA;
    _SECURITY_LOGON_SESSION_DATA = record
      Size: ULONG;
      LogonId: LUID;
      UserName: LSA_UNICODE_STRING;
      LogonDomain: LSA_UNICODE_STRING;
      AuthenticationPackage: LSA_UNICODE_STRING;
      LogonType: SECURITY_LOGON_TYPE;
      Session: ULONG;
      Sid: PSID;
      LogonTime: LARGE_INTEGER;
      LogonServer: LSA_UNICODE_STRING;
      DnsDomainName: LSA_UNICODE_STRING;
      Upn: LSA_UNICODE_STRING;
    end;
    SECURITY_LOGON_SESSION_DATA = _SECURITY_LOGON_SESSION_DATA;
  
    _WTS_INFO_CLASS = (
      WTSInitialProgram,
      WTSApplicationName,
      WTSWorkingDirectory,
      WTSOEMId,
      WTSSessionId,
      WTSUserName,
      WTSWinStationName,
      WTSDomainName,
      WTSConnectState,
      WTSClientBuildNumber,
      WTSClientName,
      WTSClientDirectory,
      WTSClientProductId,
      WTSClientHardwareId,
      WTSClientAddress,
      WTSClientDisplay,
      WTSClientProtocolType);
    WTS_INFO_CLASS = _WTS_INFO_CLASS;
  
    _WTS_CONNECTSTATE_CLASS = (
      WTSActive,              // User logged on to WinStation
      WTSConnected,           // WinStation connected to client
      WTSConnectQuery,        // In the process of connecting to client
      WTSShadow,              // Shadowing another WinStation
      WTSDisconnected,        // WinStation logged on without client
      WTSIdle,                // Waiting for client to connect
      WTSListen,              // WinStation is listening for connection
      WTSReset,               // WinStation is being reset
      WTSDown,                // WinStation is down due to error
      WTSInit);               // WinStation in initialization
    WTS_CONNECTSTATE_CLASS = _WTS_CONNECTSTATE_CLASS;
  
    function LsaFreeReturnBuffer(Buffer: pointer): Integer; stdcall;
  
    function WTSGetActiveConsoleSessionId: DWORD; external 'Kernel32.dll';
  
    function LsaGetLogonSessionData(LogonId: PLUID;
       var ppLogonSessionData: PSECURITY_LOGON_SESSION_DATA): LongInt; stdcall;
       external 'Secur32.dll';
  
    function LsaNtStatusToWinError(Status: cardinal): ULONG; stdcall;
       external 'Advapi32.dll';
  
    function LsaEnumerateLogonSessions(Count: PULONG; List: PLUID): LongInt;
       stdcall; external 'Secur32.dll';
  
    function WTSQuerySessionInformationA(hServer: THandle; SessionId: DWORD;
       WTSInfoClass: WTS_INFO_CLASS; var pBuffer: Pointer;
       var pBytesReturned: DWORD): BOOL; stdcall; external 'Wtsapi32.dll';
  
  type
    TForm1 = class(TForm)
      Button1: TButton;
      Memo1: TMemo;
      procedure Button1Click(Sender: TObject);
    private
      { Private declarations }
    public
      { Public declarations }
    end;
  
  var
    Form1: TForm1;
  
  implementation
  
  {$R *.dfm}
  
  function LsaFreeReturnBuffer; external 'secur32.dll' name 'LsaFreeReturnBuffer';
  
  procedure GetActiveUserNames(var slUserList : TStringList);
  var
     Count: cardinal;
     List: PLUID;
     sessionData: PSECURITY_LOGON_SESSION_DATA;
     i1: integer;
     SizeNeeded, SizeNeeded2: DWORD;
     OwnerName, DomainName: PChar;
     OwnerType: SID_NAME_USE;
     pBuffer: Pointer;
     pBytesreturned: DWord;
     sUser : string;
  begin
     //result:= '';
     //Listing LogOnSessions
     i1:= lsaNtStatusToWinError(LsaEnumerateLogonSessions(@Count, @List));
     try
        if i1 = 0 then
        begin
            i1:= -1;
            if Count > 0 then
            begin
                repeat
                  inc(i1);
                  LsaGetLogonSessionData(List, sessionData);
                  //Checks if it is an interactive session
                  sUser := sessionData.UserName.Buffer;
                  if (sessionData.LogonType = Interactive)
                    or (sessionData.LogonType = RemoteInteractive)
                    or (sessionData.LogonType = CachedInteractive)
                    or (sessionData.LogonType = CachedRemoteInteractive) then
                  begin
                      //
                      SizeNeeded := MAX_PATH;
                      SizeNeeded2:= MAX_PATH;
                      GetMem(OwnerName, MAX_PATH);
                      GetMem(DomainName, MAX_PATH);
                      try
                      if LookupAccountSID(nil, sessionData.SID, OwnerName,
                                         SizeNeeded, DomainName,SizeNeeded2,
                                         OwnerType) then
                      begin
                        if OwnerType = 1 then  //This is a USER account SID (SidTypeUser=1)
                        begin
                          sUser := AnsiUpperCase(sessionData.LogonDomain.Buffer);
                          sUser := sUser + '\';
                          sUser := sUser + AnsiUpperCase(sessionData.UserName.Buffer);
                          slUserList.Add(sUser);
  //                          if sessionData.Session = WTSGetActiveConsoleSessionId then
  //                          begin
  //                            //Wenn Benutzer aktiv
  //                            try
  //                                if WTSQuerySessionInformationA
  //                                   (WTS_CURRENT_SERVER_HANDLE,
  //                                    sessionData.Session, WTSConnectState,
  //                                    pBuffer,
  //                                    pBytesreturned) then
  //                                begin
  //                                    if WTS_CONNECTSTATE_CLASS(pBuffer^) = WTSActive then
  //                                    begin
  //                                      //result:= sessionData.UserName.Buffer;
  //                                      slUserList.Add(sessionData.UserName.Buffer);
  //                                    end;
  //                                end;
  //                            finally
  //                              LSAFreeReturnBuffer(pBuffer);
  //                            end;
                            //end;
                        end;
                      end;
                      finally
                      FreeMem(OwnerName);
                      FreeMem(DomainName);
                      end;
                  end;
                  inc(List);
                  try
                      LSAFreeReturnBuffer(sessionData);
                  except
                  end;
              until (i1 = Count-1);// or (result <> '');
            end;
        end;
     finally
        LSAFreeReturnBuffer(List);
     end;
  end;
  
  procedure TForm1.Button1Click(Sender: TObject);
  var
    slUsers : TStringList;
  begin
    slUsers := TStringList.Create;
    slUsers.Duplicates := dupIgnore;
    slUsers.Sorted := True;
  
    try
      GetActiveUserNames(slUsers);
      Memo1.Lines.AddStrings(slUsers);
    finally
      FreeAndNil(slUsers)
    end;
  end;
  
  end.
  

  Mick : I should add that you can turn this code into an MSI DLL and use it as a windows installer (MSI) custom action. I have an example of how to do this with Delphi here: http://stackoverflow.com/questions/367365/how-do-i-write-custom-action-dll-for-use-in-an-msi.

  Mick : If you don't have Delphi, all of this code will compile and work just fine using the free Turbo Delphi. You can download that here: http://www.codegear.com/downloads/free/turbo