What is the proper way to insert in and join these tables?-CodePudding

I have three tables: Clients, Products and Orders

Clients:

ClientID - PK
ClientName

Products:

ProductID - PK
ProductName
ProductQuantity
ProductSize
Comments

Orders:

OrderID - PK
StartDate
FinishDate
Delivered
Billed

I have some different answers about my question, which is how to join these tables. Should i create a 4th table for the id's of the 3 tables or join clients and products in the orders table. Thing is that one client has one order of one or more products. Please advise.

CodePudding user response：

If I assume correctly, that your existing tables look like this:

public class Client
{
    public int Id;
    public string Name;
}

public class Product
{
    public int Id;
    public string Name;
    public int Quantity;
    public decimal Size;
    public string Comments;
}

public class Order
{
    public int Id;
    public DateTime StartDate;
    public DateTime FinishDate;
    public DateTime Delivered;
    public DateTime Billed;
}

...then here are my initial suggestions:

Modify Order with a FK to Client so you know who placed the order.

public class Order
{
    public int Id;
    public int ClientId;
    public DateTime StartDate;
    public DateTime FinishDate;
    public DateTime Delivered;
    public DateTime Billed;
}

Then, because an order is an immutable record of a transaction, it should not link to Product. So, create a new OrderProduct table, include an FK to Order and copy all the fields each time you create an order.

public class OrderProduct
{
    public int Id;
    public int OrderId;
    public string Name;
    public int Quantity;
    public decimal Size;
    public string Comments;
}

Now, if you have a product catalogue like this:

var products = new[]
{
    new Product()
    {
        Id = 1,
        Name = "Foo"
        Quantity = 500,
    }
};

...and you sell 1 "Foo" to "Fred" then the transactions might look like this:

var product =
    products
        .Where(p => p.Name == "Foo")
        .First();

var client = new Client() { Id = 1, Name = "Fred" };

var order = new Order()
{
    Id = 1,
    ClientId = client.Id,
    StartDate = DateTime.Now
};

var orderProduct = new OrderProduct()
{
    Id = product.Id,
    OrderId = order.Id,
    Name = product.Name,
    Quantity = 1
};

product.Quantity -= 1;

CodePudding user response：

Do I understand it correctly that you have a list of products you can order and one order is always done by one single client?

Then I would create a table for the clients with their data and an id, then I would create a table with the different products and give each product an id. I would give each order the client id and the product id and what data else you may have (start date, finnish date, etc.) (An own order id can be helpful too)

Like this you can find every order easily by the client or by the product. But make sure that the ids aren't changing.