Search code examples
database-normalization

What is the best way to avoid redundancy with these tables

I am trying to make a database with a plan table.| The plans are: Basic, Express, Advanced, and Professional Each of them will have a pricetag and support plans like PHP, ASP and SSL support. 

        Basic:      Express
price - 69,00       89,00    
setup - 0,00         0,00
SSL -    X           Yes
PHP -    X           Yes
ASP -    X            X

How am I able to make an ERD without causing redundancy.

I thought about making a table like:

    Table Plans:
    ID     Pname     Pprice     Psetup     Pasp     Pphp     Pssl
    0      Basic     69,00      0,00       0        0        0
    1      Express   89,00      0,00       0        1        1
    etc..

The plans will be expanded with more types. But eventually that will make the table too large so I thought about creating another table.

    Table Plans:
    ID     Pname     Pprice     Psetup     Plansbool
    0      Basic     69,00      0,00       0
    1      Express   89,00      0,00       1


    Table Plansbool:
    ID     Bname     YesNo      PlansID
    0      Php       0          0
    1      Php       1          1
    2      Asp       0          0
    3      Asp       0          1
    4      Ssl       0          0 
    5      Ssl       1          1

But this also creates the problem that in the table "plans", the plans can only accept one of the types in the Plansbool table.. I also think this is redundant and right now I can't see the big picture of creating a non redundant plan table with the according types of support..

I'm sorry if this is really easily solved or to confusing.

Solution

  • In your idea how to solve this is no redundancy. Redundancy would be created when you are able to find the same data by using two different ways.

    I am confused by what "plans" are. Are plans Basic, Express and so on, or are plans PHP, ASP... ?

    I think you meant that PHP, ASP and so on are types (because in the first sentence you called them both plans (Basic, Express, ... and PHP, ASP, ...)

    But make a table with the plans (Basic, Express, ...) and so on and another table with the types (PHP, ASP, ...)

    Than make a many to many - connection and just resolve it with the attribut "Price" and "Stetup". enter image description here

    And the columns:

    Plan (PlanID, Name)
PlanType (PlanID, TypeID, Price, Setup)
Type (TypeID, Name)

    And just add a "PlanType" when there is a plan for a type :-)