CODEWITHSUNDEEP
architecturedatabase-design
Kamyar
Kamyar

Reputation: 18797

Database Architecture for this scenario

Well, this architectural issue has been wandering in my mind for a while. Suppose the following scenario:
I have a Licenses table. Conceptually, each license can be limited to (License Types):

So, each license should store some custom value. (Type 1: Integer, Type 2: DateTime, Type 3: null)

What's the best architecture for this scenario?

Which one do you suggest to be more conventional? Do you suggest any other way to implement it?

UPDATE: Just found out about Sparse columns is SQL Server. Sounds very promising...

Upvotes: 0

Views: 180

Answers (3)

Demian Brecht
Demian Brecht

Reputation: 21368

Off the top of my head (I haven't implemented anything like this yet), I might do something like this:

Create database tables for Customer and License. Within Customer, along with all of the other generic customer information I would add a column for licenseType, which would reference the License table.

License would store the licenseId as well as any relevant meta data. This would not include license-specific rules.

On the code side, I would create a LicenseFactory class which would create an instance of a license interface (ILicense). ILicense might look something like this (in PHP):

interface ILicense
{
    public isValid($customer);
}

Then, I would have license-specific implementations:

class TrialLicense implements ILicense
{
    public isValid($customer)
    {
        // business logic for this specific license type here
    }
}

The factory class would look something like:

class LicenseFactory
{
    public static function getInstance($type)
    {
        switch($type)
        {
            case 0:
                return new TrialLicense();
                break;
        }
    }
}

So now, my application code might look something like this:

public function isLicenseValid($customer)
{
    return LicenseFactory::getLicense($customer->licenseType)->isValid($customer);
}

None of this has been tested and are just my (rather long winded) initial thoughts. Hope it helps a little though (even though your app may not be php driven :))

Edit: Forgot to mention - the power in this approach is the extensibility. Any time you want to add a new license type, you'd simply add a new row to the License table and a new ILicense implementation with whatever business rules are required.

Upvotes: 1

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

You can have a single table as a parent and three other tables referencing the base table extending the schema.

That's the first option.

Another option is using a simple NoSql Database with low footprint or an OO database like db4o or even simple data stores like an encrypted XML file.

Upvotes: 0

iehrlich
iehrlich

Reputation: 3592

You should keep a separate table for each license type:

LicenseID(PK)---Type

LicenseID(FK)---EndDate

LicenseID(FK)---TryTimes

and also maintain data integrity such as only time-trial licenses would come into the second table.

This will be also quite a scalable model, you will need to add one more table, one more integrity rule and one more interface in order to add another license type.

Upvotes: 1

Related Questions