Help with designing a multi relational schema / domain model

Question

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.

Answer 1

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