Help with designing a multi relational schema / domain model

database-designentity-framework

I am in the process of developing a web application with asp.net MVC. I am using Entity Framework to make a connection to my database.

I am having trouble with a specific concept in my program. I will explain below.

I have a Network class which resembles a network in real life which has a frequency.

I then have a Block class. A block is a certain section of devices on a networks.

I also have a Customers Class.

Here are my current Models of the above classes:

Network:

  public class Network
{
    public int networkID { get; set; }
    public String name { get; set; }
    public int startCode { get; set; }
    public decimal frequency { get; set; }
    public virtual List<Block> blocks { get; set; }
}

Customer:

 public class Customer
{
    public int customerID { get; set; }
    public String name { get; set; }
    public String shortName { get; set; }
}

Block:

 public class Block
{
    public int blockID { get; set; }      
    public decimal start { get; set; }      
    public int end { get; set; }    
    public int blockSize { get; set; }
    public Customer customer { get; set; }
}

Ok now my problem comes with this.

A network has many blocks.
Each block belongs to a single client.
A client may have many blocks in one network, and can also have many blocks in many networks.

Is my above configuration correct / sufficient to store my certain scenario. But I also then need to be able To browse a client and query all his blocks which he has.

Best Answer

It might help if you also had some table descriptions in your question, but here goes...

A network has many blocks.

You could do this very simply with a network_id in each block. That way there is no restrictions on how many blocks can reference a network, but you ensure that each block is only in one network.

Each block belongs to a single client. A client may have many blocks in one network, and can also have many blocks in many networks.

Having a client_id in your block would ensure that each block can only reference one client, but a single client could be referenced by many blocks (and it won't matter which network they're in). So maybe your block table needs to look something like this:

block
-----
  ID
  client_id (FK to client.id)
  network_id (FK to network.id)
  [other fields...]

I also then need to be able To browse a client and query all his blocks which he has

The SQL for that could look something like:

select client.name, client.id, block.id
from block
inner join client
on client.id = block.client_id
where client.id = $SOME_PARAMETER_FOR_CLIENT_ID

Related Solutions

Questionnaire database design – which way is better

Definitely do not hard code your questionnaire. Use a relational database or xml files. I propose the following tables

Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.
Section: The sections a questionnaire is made up. Number of the section, section title, description.
Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).
Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.
Respondent: The persons answering the questions. Personal data, user number.
Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire (or if the survey is anonymous), this table is obsolete and can be merged with the Respondent table. Interview date (or test date or survey date), interviewer (if it applies).
Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).
Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach; however, you could decide to concatenate choices into one string or to store them as bit pattern or simplify it in some other way depending on your needs.

Derived classes with same base class and class specific ids

You can simply inherit the primary key from the Animals table in the Fish and Cattle table.

This example uses syntax that is SQL Server specific, however you should be able to convert this into SQLLite syntax:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);

This design prevents the need for surrogate key in the Fish table, while still ensuring uniqueness for the rows in that table by way of the unique constraint UQ_Fish.

If you want to see all the details about Fish, you'd use a query similar to:

SELECT Animals.AnimalID
    , Animals.DateCreated
    , Fish.FinCount
FROM dbo.Animals
    INNER JOIN dbo.Fish ON Animals.AnimalID = Fish.AnimalID;

Alternatively, if you are constrained by the classes built by the app, you can simply create the following structure:

CREATE TABLE dbo.Animals
(
    AnimalID INT NOT NULL
        CONSTRAINT PK_Animals
        PRIMARY KEY CLUSTERED
    , DateCreated DATETIME
);

CREATE TABLE dbo.Fish
(
    FishID INT NOT NULL
        CONSTRAINT PK_Fish
        PRIMARY KEY CLUSTERED
    , AnimalID INT NOT NULL
        CONSTRAINT FK_Fish_AnimalID
        FOREIGN KEY
        REFERENCES dbo.Animals(AnimalID)
    , FinCount INT NOT NULL
    , CONSTRAINT UQ_FISH
        UNIQUE (AnimalID)
);

Best Answer

Related Solutions

Questionnaire database design – which way is better

Derived classes with same base class and class specific ids

Related Question