Database Schema suggestions please

Question

I have a scenario and i'm confused about how i can go about designing the database schema for it.

In my software (php) there are companies and applications.

companies need to have licenses to access applications.

now the fields (for form while purchasing licenses) for each application is different.

for ex:

for application1:

fields are:

1. no of users
2. no of groups

for application2:

1. no of users

for application3:

1. number of hours of usage

Prices are based on these fields.

Now i need to design schema for this so that on one page company can manage licenses for all applications.

How can i make this schema generic?

Please help. Thanks.

Answer 1

You can go with this type of structure

select * from applicationMaster



| APPID |      APPNAME |
------------------------
|     1 | Application1 |
|     2 | Application2 |

ApplicationMaster will go with main Application related details which won't be repeated such Name, date etc.

Query 2:

select * from applicationField



| FIELDID | APPID |   FIELDNAME |
---------------------------------
|       1 |     1 |   NoOfUsers |
|       2 |     1 |  NoOfGroups |
|       3 |     2 | NoHourusage |

ApplicationField can adjust any number of field for a particular appId. So AppId 1 has 2 fields NoofUsers and NoOfGroups. It is also capable to adjust newer fields for a particular app if you want.

Query 3:

ApplicationValue will have the values for every license aplication so it will have compId which represents which company has applied using fieldId which refers to applicationField table we can get for which app values are stored.

select * from applicationValue



| ID | COMPID | FIELDID | FIELDVALUE |
--------------------------------------
|  1 |      1 |       1 |         50 |
|  2 |      1 |       2 |        150 |
|  3 |      2 |       3 |        350 |
|  4 |      3 |       1 |        450 |
|  5 |      3 |       2 |         50 |

applicationPriceMaster stores the price package for each application. There could be multiple package for a application.

select * from applicationPriceMaster


| APPPACKAGE | APPID | TOTALPRICE |
-----------------------------------
|          1 |     1 |         50 |
|          2 |     1 |        100 |

For each application package its details will posted in this table.

select * from applicationPriceDetail


| APPPACKAGE | FIELDID | QUANT |
--------------------------------
|          1 |       1 |     1 |
|          1 |       2 |     1 |
|          2 |       1 |    10 |
|          2 |       2 |     1 |

NOTE Please check the structure as it is now too complex and check what type of queries you would be running on these table and its performance.

select apm.APPPACKAGE, TOTALPRICE from
applicationPriceMaster apm
inner join 
(select APPPACKAGE from applicationPriceDetail
where FIELDID=1 and QUANT=1)a
on apm.APPPACKAGE = a.APPPACKAGE
inner join
(select APPPACKAGE from applicationPriceDetail
where FIELDID=2 and QUANT=1)b
on 
a.APPPACKAGE=b.APPPACKAGE

SQL FIDDLE:

| APPPACKAGE | TOTALPRICE |
---------------------------
|          1 |         50 |

For single filter you have to use this query, so you have to increase number of inner query with the number of inner filter.

select apm.APPPACKAGE, TOTALPRICE from
applicationPriceMaster apm
inner join 
(select APPPACKAGE from applicationPriceDetail
where FIELDID=1 and QUANT=1)a
on apm.APPPACKAGE = a.APPPACKAGE

NOTE-This query is quite complex and will only work if the values are same as mentioned in the packagedetail table and will work only if the values are 2 filter you have to remove 1 inner join if there is only 1 filter. So I suggest you to reconsider before using this approach.

Answer 2

What you have there, could be easily mapped to Classes in an OO language (like PHP). You have an Abstract License, and then 3 Subclasses (ApplicationByUsersAndGroups, etc). Then, mapping to a Relational database is a very common problem, here is a nice article about it: http://www.ibm.com/developerworks/library/ws-mapping-to-rdb/

It has 3 options, it depends on the way you want to structure your application which one you should use. I recommend reading it, it is not that long.

Answer 3

how about a table structured this way:

LicenseId int  PK
CompanyId Int  PK 
AppId     Int  PK
LicenseType int
NumberOfUsers int
NumberOfGroups int
NumberOfHours int
Price    Money

Depending on LicenseType, you will use different column in your business logic, you might need to add CompanyID and/or AppID, that depends how you going to structure those tables as well as relation ships between company/app/license.

Some questions to think about:

• Can one company have different License Types for same App?
• Can one company have different Apps?

Answer 4

One way is

Table LICENCES:
LICENSE_ID ==> UNIQUE IDENTIFIER
COMPANY_ID ==> references table COMPANIES
APPLICATION_ID ==> references table APPLICATIONS
LICENCE_TYPE ==> either of "BY_GROUPS_AND_USERS", "BY_USERS", "BY_HOURS"
LICENCE_BODY_ID ==> ID of specific body table
[...]

Table LIC_TYPE_BY_GROUPS_AND_USERS: 
LICENCE_BODY_ID ==> body identifier
NO_GROUP 
NO_USERS
[...]

Table LIC_TYPE_BY_USERS: 
LICENCE_BODY_ID ==> body identifier
NO_USERS
[...]

This way, your intention is clear. Even after long time comming back, you will know in no time how things are organized, which fields are used in which case...

Answer 5

Dont complicate things, if the number of users is unlimited then set it to 999999 or some other max value.

This keeps the license check logic (which will run every time a user logs in ) simple and the same for all applications.

You will need extra logic in the licenses maintenance application, but this should also be pretty simple: if the cost_per_user is = 0 then set no_of_users = 99999

Again you end up with the same licensing screen and logic for all your applications.