Category Archives: Data modelling

I’ve used OpenAI to generate code for me!

Posted on by

Here is my conversation with OpenAI where I tell the artificial intelligence to create code for me. What I want is a database with a table that holds two hashes and the file size so I can check if a file is unique or not.

It’s simple, but quite effective and took me less than 30 minutes to get the final code. Will it work? Well, I’m going to test that soon enough. My input is in bold fonts.

I need to compare if a file is identical to another file. For this I want to calculate over the hash. And then calculate a second hash over the same file with a different algorithm. Both hashes are then stored in a C# class together with the file size in bytes. Write this code for me in C#.

using System;
using System.Security.Cryptography;

public class FileHash
{
public string Hash1 { get; set; }
public string Hash2 { get; set; }
public long FileSize { get; set; }

public FileHash(string filePath)
{
    // Calculate hash of file using SHA256
    using (SHA256 SHA256 = SHA256.Create())
    {
        byte[] hashBytes;
        using (FileStream fs = new FileStream(filePath, FileMode.Open))
        {
            hashBytes = SHA256.ComputeHash(fs);
        }

        // Store hash in Hex format
        this.Hash1 = BitConverter.ToString(hashBytes).Replace("-", "");
    }

    // Calculate hash of file using SHA512
    using (SHA512 SHA512 = SHA512.Create())
    {
        byte[] hashBytes;
        using (FileStream fs = new FileStream(filePath, FileMode.Open))
        {
            hashBytes = SHA512.ComputeHash(fs);
        }

        // Store hash in Hex format
        this.Hash2 = BitConverter.ToString(hashBytes).Replace("-", "");
    }

    // Get file size
    this.FileSize = new FileInfo(filePath).Length;
}

}

Use two different hashing algorithms. Add salt.

using System;
using System.Security.Cryptography;
using System.IO;

public class FileHash
{
    public string Hash1 { get; set; }
    public string Hash2 { get; set; }
    public long FileSize { get; set; }

    public FileHash(string filePath, string salt)
    {
        // Calculate hash of file using SHA256
        using (SHA256 SHA256 = SHA256.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA256.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA512
        using (SHA512 SHA512 = SHA512.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA512.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this.FileSize = new FileInfo(filePath).Length;
    }
}


Do not use SHA256.

using System;
using System.Security.Cryptography;
using System.IO;

public class FileHash
{
    public string Hash1 { get; set; }
    public string Hash2 { get; set; }
    public long FileSize { get; set; }

    public FileHash(string filePath, string salt)
    {
        // Calculate hash of file using MD5
        using (MD5 MD5 = MD5.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = MD5.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA1
        using (SHA1 SHA1 = SHA1.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA1.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this.FileSize = new FileInfo(filePath).Length;
    }
}

Make fields of the properties. Add a constant “Knowledge And Technology Joyfully Engaged is salting this data.” Us this as salt.

using System;
using System.Security.Cryptography;
using System.IO;

public class FileHash
{
    private string _hash1;
    private string _hash2;
    private long _fileSize;
    public string Hash1 { get { return _hash1; } }
    public string Hash2 { get { return _hash2; } }
    public long FileSize { get { return _fileSize; } }

    public const string Salt = "Knowledge And Technology Joyfully Engaged is salting this data.";

    public FileHash(string filePath)
    {
        // Calculate hash of file using MD5
        using (MD5 MD5 = MD5.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = MD5.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this._hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA1
        using (SHA1 SHA1 = SHA1.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA1.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this._hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this._fileSize = new FileInfo(filePath).Length;
    }
}

Make the salt private. Remove the backing fields.

using System;
using System.Security.Cryptography;
using System.IO;

public class FileHash
{
    public string Hash1 { get; private set; }
    public string Hash2 { get; private set; }
    public long FileSize { get; private set; }
    public const string Salt = "Knowledge And Technology Joyfully Engaged is salting this data.";

    public FileHash(string filePath)
    {
        // Calculate hash of file using MD5
        using (MD5 MD5 = MD5.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = MD5.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA1
        using (SHA1 SHA1 = SHA1.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA1.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this.FileSize = new FileInfo(filePath).Length;
    }
}

What will each hash look like?

The hashes will be a string of 32 hexadecimal characters, with each character being a combination of 0-9 and A-F.

Add an unique Guid as identifier. Make this class ready to be used in Entity Framework 6.

public class FileHash
{
    public Guid Id { get; set; }
    public string Hash1 { get; private set; }
    public string Hash2 { get; private set; }
    public long FileSize { get; private set; }
    public const string Salt = "Knowledge And Technology Joyfully Engaged is salting this data.";

    public FileHash(string filePath)
    {
        this.Id = Guid.NewGuid();

        // Calculate hash of file using MD5
        using (MD5 MD5 = MD5.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = MD5.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA1
        using (SHA1 SHA1 = SHA1.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA1.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this.FileSize = new FileInfo(filePath).Length;
    }
}

Replace the constructor by a class factory.

public class FileHash
{
    public Guid Id { get; set; }
    public string Hash1 { get; private set; }
    public string Hash2 { get; private set; }
    public long FileSize { get; private set; }
    public const string Salt = "Knowledge And Technology Joyfully Engaged is salting this data.";

    private FileHash(string filePath)
    {
        this.Id = Guid.NewGuid();

        // Calculate hash of file using MD5
        using (MD5 MD5 = MD5.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = MD5.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA1
        using (SHA1 SHA1 = SHA1.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA1.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this.FileSize = new FileInfo(filePath).Length;
    }

    public static FileHash Create(string filePath)
    {
        return new FileHash(filePath);
    }
}

Create a DBContext.

public class FileHashContext : DbContext
{
    public DbSet<FileHash> FileHashes { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=localhost;Database=FileHashDatabase;User ID=sa;Password=password;");
    }
}

Use MariaDB. Server is “katje.org”, database is “FileData”, user is “FileManager”, password is “123456”.

public class FileHashContext : DbContext
{
    public DbSet<FileHash> FileHashes { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMariaDB("Server=katje.org;Database=FileData;User ID=FileManager;Password=123456;");
    }
}

Make the Id field unique. Add an unique index to the combined values of Hash1, Hash2 and FileSize.

public class FileHash
{
    [Key]
    public Guid Id { get; set; }
    public string Hash1 { get; private set; }
    public string Hash2 { get; private set; }
    public long FileSize { get; private set; }
    public const string Salt = "Knowledge And Technology Joyfully Engaged is salting this data.";

    private FileHash(string filePath)
    {
        this.Id = Guid.NewGuid();

        // Calculate hash of file using MD5
        using (MD5 MD5 = MD5.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = MD5.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash1 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Calculate hash of file using SHA1
        using (SHA1 SHA1 = SHA1.Create())
        {
            byte[] hashBytes;
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                hashBytes = SHA1.ComputeHash(fs);
            }

            // Add salt to hash
            byte[] saltBytes = Encoding.UTF8.GetBytes(Salt);
            byte[] saltedHashBytes = new byte[hashBytes.Length + saltBytes.Length];
            Array.Copy(hashBytes, saltedHashBytes, hashBytes.Length);
            Array.Copy(saltBytes, 0, saltedHashBytes, hashBytes.Length, saltBytes.Length);

            // Store hash in Hex format
            this.Hash2 = BitConverter.ToString(saltedHashBytes).Replace("-", "");
        }

        // Get file size
        this.FileSize = new FileInfo(filePath).Length;
    }

    public static FileHash Create(string filePath)
    {
        return new FileHash(filePath);
    }
}

public class FileHashContext : DbContext
{
    public DbSet<FileHash> FileHashes { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<FileHash>().HasIndex(x => new { x.Hash1, x.Hash2, x.FileSize }).IsUnique();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMariaDB("Server=katje.org;Database=FileData;User ID=FileManager;Password=123456;");
    }
}

What does the table look like? Give me the CREATE SQL code.

CREATE TABLE FileHash (
    Id BINARY(16) NOT NULL PRIMARY KEY,
    Hash1 VARCHAR(128) NOT NULL,
    Hash2 VARCHAR(128) NOT NULL,
    FileSize BIGINT NOT NULL,
    UNIQUE INDEX (Hash1, Hash2, FileSize)
);

Summarize the code.

FileHash is a class that stores the hashes and file size of a file. It uses the MD5 and SHA1 algorithms to generate the hashes, and also adds salt to the hashes for increased security. The FileHashContext class is a database context that uses MariaDB to store the FileHash objects. The database contains a table with columns for the Id, Hash1, Hash2, and FileSize, and an index that ensures that each combination of hashes and file sizes is unique.

It amazes me with how little instructions I managed to get all this code. And it looks okay to me. Thing is, I’m going to make some manual changes to this code to make it more my style, but having OpenAI create most of this code for me is definitely going to save me a lot of time in the future!

Will OpenAI and other AI replace developers? That is unlikely, as developers still have to evaluate the resulting code and someone has to actually tell the AI to what it needs to make. And while telling the AI to create it all, I also had to check the code over and over again and have the AI replace code I did not like.

A very generic datamodel.

Posted on by

I’ve come up with several projects in the past and a few have been mentioned here before. For example, the Garagesale project which was based on a system I called “CART”. Or the WordChain project that was a bit similar in structure. And because those similarities, I’ve been thinking about a very generic datamodel that should be handled to almost any project.

The advantage of a generic database is that you can focus on the business layer while you don’t need to change much in the database itself. The datamodel would still need development but by using the existing model, mapping to existing entities, you could keep it all very simple. And it resulted in this Datamodel:ClassDiagram(Click the image to see a bigger version.)

The top class is ‘Identifier’ which is just an ID of type GUID to find the records. Which will work fine in derived classes too. Since I’m using the Entity Framework 6 I can just use POCO to keep it all very simple. All I have to do is define a DBContext that tells me which tables (classes) I want. If I don’t create an entry for ‘Identifier’, the table won’t be created either.

The next class is the ‘DataContent’ class, which can hold any XML. That way, this class can contain all information that I define in code without the need to create new tables. I also linked it to a ‘DataTemplate’ class which can be used to validate the content of the XML with an XML schema or special style sheet. (I still need to work out how, exactly.) The template can be used to validate the data inside the content.

The ‘BaseItem’ and ‘BaseLink’ classes are the more important here. ‘BaseItem’ contains all fixed data within my system. In the CART system, this would be the catalog. And ‘BaseLink’ defines transactions of a specific item from one item to another. And that’s basically three-fourth of the CART system. (The template is already defined in the ‘DataTemplate’ class.)

I also created two separate link types. One to deal with fixed numbers which is called ‘CountLink’ which you generally use for items. (One cup, two girls, etc.) The other is for fractional numbers like weights or money and is called ‘AmountLink’. These two transaction types will be the most used transaction types, although ‘BaseLink’ can be used to transfer unique items. Derived links could be created to support more special situations but I can’t think of any.

The ‘BaseItems’ class will be used to derive more special items. These special items will define the relations with other items in the system. The simplest of them being the ‘ChildItem’ class that will define more information related to a specific item. They are strongly linked to the parent item, like wheels on a car or keys on a keyboard.

The ‘Relation’ class is used to group multiple items together. For example, we can have ‘Books’ defined as relation with multiple book items linked to it. A second group called ‘Possessions’ could also be created to contain all things I own. Items that would be in both groups would be what is in my personal library.

A special relation type is ‘Property’ which indicates that all items in the relation are owned by a specific owner. No matter what happens with those items, their owner stays the same. Such a property could e.g. be a bank account with a bank as owner. Even though customers use such accounts, the account itself could not be transferred to some other bank.

But the ‘Asset’ class is more interesting since assets are the only items that we can transfer. Any transaction will be about an asset moving from one item to another. Assets can still be anything and this class doesn’t differ much from the ‘BaseItem’ class.

A special asset is a contract. Contracts have a special purpose in transactions. Transactions are always between an item and a contract. Either you put an asset into a contract or extract it from a contract. And contracts themselves can be part of bigger contracts. By checking how much has been sent or received to a contract you can check if all transactions combined are valid. Transactions will have to specify if they’re sending items to the contract or receiving them from the contract.

The ‘BaseContract’ class is the more generic contract type and manages a list of transactions. When it has several transactions, it is important that there are no more ‘phantom items’. (A phantom item would be something that’s sent to the contract but not received by another item, or vice versa.) These contracts will need to be balanced as a check to see if they can be closed or not. They should be temporary and last from the first transaction to the last.

The ‘Contract’ type derived from ‘BaseContract’ contains an extra owner. This owner will be the one who owns any phantom items in the contract. This reduces the amount of transactions and makes the contract everlasting. (Although it can still be closed.) Balancing these contracts is not required, making them ideal as e.g. bank accounts.

Yes, it’s a bit more advanced than my earlier CART system but I’ve considered how I could use this for various projects that I have in mind. Not just the GarageSale project, but also a simple banking application, a chess notation application, a project to keep track of sugar measurements for people with diabetics and my WordChain application.

The banking application would be interesting. It would start with two ‘Relation’ records: “Banks” and “Clients”. The Banks relation would contain Bank records with information of multiple banks. The Clients relation would contain the client records for those banks. And because of the datamodel, clients can have multiple banks.

Banks would be owners of bank accounts, and those accounts would be contracts. All the bank needs to do is keep track of all money going in our out the account. (Making money just another item and all transactions will be of type ‘AmountLink’.) But to link those accounts to the persons who are authorized to receive money from the account, each account would need to be owner of a Property record. The property record then has a list of clients authorized to manage the account.

And we will need six different methods to create transactions. Authorized clients can add or withdraw money from the account. Other clients can send or receive payments from the account, where any money received from the contract needs to be authorized. Finally, the bank would like to have interest, or pays interest. (Or not.) These interest transactions don’t need authorization from the client.

The Chess Notation project would also be interesting. It would start with a Board item and 64 squares items plus a bunch of pieces assets. The game itself would be a basic contract without owner. The Game contract would contain a collection of transactions transferring all pieces to their first locations. A collection of ‘Move’ contracts would also be needed where the Game Contract owns them. The Move would show which move it is (including branches of the game) and the transactions that take place on the board. (White Rook gone from A1, White Rook added to A4 and Black pawn removed from A4, which translates into rook takes pawn at A4.)

It would be a very complex way to store a chess game, but it can be done in the same datamodel as my banking application.

With the diabetes project, each transaction would be a measurement. The contract would be owned by the person who is measuring his or her blood and we don’t need to send or receive these measurements, just link them to the contract.

The WordChain project would be a bit more complex. It would be a bunch of items with relations, properties and children. Contracts and assets would be used to support updates to the texts with every edit of a WordChain item kicking the old item out of the contract and adding a new item into the contract. That would result in a contract per word in the database.

A lot of work is still required to make sure it works as well as I expect. It would not be the most ideal datamodel for all these projects but it helps me to focus more on the business layer and the GUI without worrying about any database changes. Once the business model becomes more advanced, I could create a second data layer with a better datamodel to improve the performance of the data management.

 

 

 

The CART datamodel

Posted on by

Well, my back problems made me think a lot about the CART system that I’ve mentioned before. And it made me consider how I need to handle the difference between plain data and the relationship between the objects. Because the most troubling thing I had to deal with was that I did not want to change my datamodel for every new item that I want to store. So it made me think…

The CART system is mostly created to handle relationships between items, transactions and contracts. It’s not about handling of the data itself. Actually, the system doesn’t even care about the data. All that matters are the relationships. Data is just something to display to the user, something to store but normally not something that you’ll need to process very often at the data layer. So, considering the fact that you can serialize objects in .NET to XML data, I’ve decided to support a basic structure for my Garage Sale project for all the items, transactions and contracts. And each of them will contain a Data property that has the serialized data, that I could convert to data objects and back again.

This idea makes it also more clear where the templates are within my system. My templates are these object definitions! I will have a very generic database with a very simple layout, and I can generate a very complex business layer around this all that I can change as often as I like without the need to change my database model. As a result, I might never have to change the data model again!

Of course it will have a drawback, since the database will contain serialized objects. If I change those objects, I will also need to keep track of the changes in those stored structures and either update them or keep up multiple versions of those objects. Updating those structures would mean that I have to create update apps that know both the old structures and the new structures. It should then convert each old structure to a new structure. Maintaining multiple versions might be more practical since that would leave all old data intact in your system. Anyways, it’s some added complexity that I’ll have to consider.

But now, my datamodel as created by Visual Studio 2012 by using the Entity Framework:EF-CART

 

So, what do you see?

  • The DataObject is the base class for any CART object. It has a unique identifier, a name that can be used to show the related object and a Data property that will contain an object as XML.
  • DataItem is a generic item class, containing an additional description just for more practical reasons. When a user wants to select an existing item, having a description makes it possible to avoid reading the object within the data.
  • The Collection table is just an item with an extra bonus. It can contain multiple child items without the need for any transactions. Actually, this is just a shortcut solution to allow more complex structures within your items. For example, you might create a complete Household collection containing husband, wife, four children and a dog. And although you could link them together by using transactions, having them in a collection just saves the need to create those transactions.
  • DataTransactions is the base class for the transactions, having a sender, receiver and subject item connected together. It also has a link to a rule and a timestamp, indicating when the transaction tool place. (Or will take place for future transactions.)
  • IntegerTransaction is just a basic transaction with a multiplier. This way, you don’t have to add a lot of transactions when your customer buys ten bags of flour.
  • DecimalTransaction is also a basic transaction that will handle items that can be traded in all kinds of different numbers, including fractional amounts. For example, the price of a product, or it’s weight, length or light intensity.
  • DataRule is the basic contract type. It’s a collection of transactions that are all related to one another. For example, the sale of a product would result in a sale rule.
  • The Contract class is more than just a rule. It’s a rule that can contain child rules, thus allowing structured contracts that are made up of several subcontracts. These are used when you have to deal with complex
    situations, like mortgages. A mortgage would include a rule for purchasing a house, a rule for lending money and paying it back, plus other rules for all kinds of insurances.

Now, as I’ve said before, this datamodel should offer me more than enough structural parts to use for my Garage Sale project. All I need to do is compile it and then just leave it alone. There should not be a need to include anything else.

Well, okay… That’s not completely true, since I might want to handle user accounts and store large images of products. But these things would actually require new database structures and should preferably be part of separate databases.

Looking back at this design, it surprises even me how less data it actually has. But the trick here is that I’ve separated the relationships between objects from the actual data itself. Each object can still contain a huge amount of data. But it’s just not important for the model itself.

The Wordchain data model.

Posted on by

In my post “Project born from pain…” I’ve started a new project, which now has the name ‘WordChain’. It basically chains words together. In my post “The first design of “WordChain”…” I worked out the first design of this project, with pen and paper. And now it’s about time to consider the data model for this project, which will be the most important part of the project. And to do this, I will have to look back at the design…

WordChain screenThe design has several areas that I’ve marked with letters. As it turns out, I detect 11 different items which indicates things aren’t going to be very complex. Some of this data is from the configuration while other data is from the database. And perhaps some data is a mixture.

So, back to the root of the project. The user selects a word and something about that word will be displayed. The word will be shown in B and is related to the description in G and the image in H. However, other words can also be related to the same description so we have an m:1 relationship between word B and C and description G. Each word has just one description, but descriptions have multiple words.

Description G will have a main image and possible other images that are shown in J. Since these images are most likely shared with other descriptions we have an m:1 relationship between description G and image H but an m:n relationship between description G and image J. Each description will have one main description and multiple other images while every image will have multiple descriptions.

The description will most likely relate to other words in the system, but those words would lead to other descriptions. Or they will lead to external sites. These words are most likely part of the description and will be part of other descriptions too. So we have a second m:n relationship between description G and word E and F.

The phrases in I would be ‘special’ words so they would have their own description. They should not be shown as aliases in C nor as words in E and F.

We also have the icon A but that should be part of the configuration, just like the footer K. But the icon A might be overridden by the description. So the description should include a reference to an icon.

And we have the tabs D and these are also part of the configuration. They will link directly to a specific word. Also keep in mind that the root page must also default to a specific word to be displayed, else the visitor has no starting point. This default will be considered an “invisible” tab, but technically we can link to it from icon A, making the icon the first of all tabs.

So we have A, D and K being part of the configuration. A would be a URL, D would be a list of words and K is an HTML formatted text. Next we have words and phrases B, C, E, F and I that are linked to description G through two different connections. One will indicate the description that describes the word, the other is for related words for a specific description. Finally we have the images H and J that are linked again to the description G.

This means we have a Word table for B, C, E, F and I, an Image table for H and J and a description table for G. And we have a configuration record for the default (root) word, icon A, tabs D and footer K. And in the background three tables to connect word to description, description to word and description to image for the multiple relationships.

As it turns out, this is a simple datamodel. However, words and phrases aren’t identical and must be treated differently. In an object model, this could be done by creating a base class and derive a Word class and a Phrase class. The base class would support the relations with the description and the child class determines where it will be shown.

A base class for description would also be practical since that would allow me to create more special descriptions like input forms and whatever else I’d like. This is something I will keep in mind for the future.

A base class for images would allow me to make sub classes if I want to add videos, music or other media. So that will be my third base class.

And with this information I can start Visual Studio and create the Entity Framework model. But that will have to wait since my back is killing me again…