Dapper с магазинами и плательщиками

Я живу в зданиях, в которых есть 50 человек, и на каждом этаже делится на 2 группы, каждая группа примерно из 9 человек. Я пытаюсь создать приложение Xamarin (IOS, ANDROID, Win10, возможно веб-приложение), чтобы нам было проще общаться.

Объясняя, как я делал свою БД как есть:

Таблица покупок {если кто-то может опубликовать ее и отслеживать, как платят «плательщикам»} я планировал создать группу для каждой базы данных, я знаю, что это плохая обработка данных, поэтому я сделал идентификатор коллекции для каждого group.i сделал 2 пользовательские переменные, чтобы я мог узнать, как и как нужно платить (в противном случае я получаю только одно имя)

Я использую Dapper с двумя списками: покупки и плательщики

Модель данных

 public class Shoping
{
    public int ID { get; set; }
    public int UserID { get; set; }
    public string UserName { get; set; }
    public decimal Price { get; set; }
    public string Discretion { get; set; }
    public DateTime ProchDate { get; set; }
    public string PayURL { get; set; }
    public List<Payer> Payers { get; set; } = new();

}
public class Payer
{
    public int ID { get; set; }
    public int ShopID { get; set; }
    public string UserName { get; set; }
    public int UserID { get; set; }
    public decimal Price { get; set; }
    public decimal Payed { get; set; }
    public decimal Topay { get; set; }
}
public class User_collection
{
    public string ID { get; set; }
}
public class User
{
    public int ID { get; set; }
    public string CollectionID { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }

}

Таблицы MySQL

`shop`  (`ID`, `UserID`, `ProchDate`, `Price`, `PayURL`, `Discretion`)

`payer` (`ID`, `ShopID`, `UserID`, `Payed`)

`user`  (`ID`, `UserName`, `pas`, `CollectionID`, `phonenumber`)

`user_collection`(`ID`)

Наконец, это мой sql-запрос и код С #:

  public  Response<List<Shoping>> GetShop_GropID(string _CollectionID)
    {
     
        using (IDbConnection cnn = new MySqlConnection(c))
        {
            var p = new
            {
                CollectionID = _CollectionID
            };

            string sql = @"SELECT  S.* ,  us.UserName, P.* , up.UserName , s.Price , 
             (s.Price /(  SELECT  COUNT(DISTINCT payer.ID) FROM payer  WHERE ShopID = s.ID  ) - p.Payed ) AS Topay
                           FROM
                               shop S
                             JOIN payer P ON S.ID = p.ShopID 
                             JOIN USER up ON p.UserID = up.ID
                             JOIN USER us ON s.UserID = us.ID
                                     where 
                                           us.CollectionID = @CollectionID ;";
            var lookup = new Dictionary<int, Shoping>();

            _ = cnn.Query<Shoping, Payer, Shoping>
                (sql,
                (s, a) => {
                    Shoping shop;
                if (!lookup.TryGetValue(s.ID, out shop))
                {
                    lookup.Add(s.ID, shop = s);
                }
                if (shop.Payers == null)
                    shop.Payers = new List<Payer>();
                shop.Payers.Add(a);
                return shop;
                }  ,p).AsQueryable();
            var l = new List<Shoping>();
            l = (from s in lookup select s.Value).ToList();
            return new Response<List<Shoping>>(ResponseResult.Success, l);
        }
       
    }

Выходные данные

{
 "timestamp": 132640059769755780,
 "result": 1,
 "message": "Success",
 "content": [
  {
    "id": 1,
    "userID": 1,
    "userName": "Ror",
    "price": 100,
    "discretion": "ssss",
    "prochDate": "2021-04-13T00:00:00",
    "payURL": null,
    "payers": [
      {
        "id": 1,
        "shopID": 1,
        "userName": "Ror",
        "userID": 1,
        "price": 100,
        "payed": 0,
        "topay": 25
      },
      {
        "id": 2,
        "shopID": 1,
        "userName": "ror1",
        "userID": 2,
        "price": 100,
        "payed": 0,
        "topay": 25
      },
      {
        "id": 3,
        "shopID": 1,
        "userName": "ror1",
        "userID": 2,
        "price": 100,
        "payed": 10,
        "topay": 15
      },
      {
        "id": 4,
        "shopID": 1,
        "userName": "nan",
        "userID": 3,
        "price": 100,
        "payed": 10,
        "topay": 15
      }
    ]
  },
  {
    "id": 2,
    "userID": 1,
    "userName": "Ror",
    "price": 30,
    "discretion": "weq",
    "prochDate": "2021-04-05T00:00:00",
    "payURL": null,
    "payers": [
      {
        "id": 5,
        "shopID": 2,
        "userName": "nan",
        "userID": 3,
        "price": 30,
        "payed": 10,
        "topay": 20
      }
    ]
  },
  {
    "id": 3,
    "userID": 1,
    "userName": "Ror",
    "price": 30,
    "discretion": "weq",
    "prochDate": "2021-04-05T00:00:00",
    "payURL": null,
    "payers": [
      {
        "id": 6,
        "shopID": 3,
        "userName": "nan",
        "userID": 3,
        "price": 30,
        "payed": 10,
        "topay": 20
      }
    ]
  }
]
}

Есть ли лучший способ структурировать выходные данные или SQL-запрос? Могу ли я столкнуться с проблемами в будущем?

Когда я использую запрос SELECT S.* , P.* , us.UserName, up.UserName , s.Price , Я получаю только имя плательщика, но не покупателя. Так что я использую SELECT S.* , us.UserName, P.* , up.UserName , s.Price .

Приложение будет пользователем с группами, поэтому каждые 9 пользователей будут использовать индексированный идентификатор коллекции. Я ограничиваю поиск двумя объединениями, например:

 Select * from shoping s
 JOIN USER u ON s.UserID = u.ID 
 JOIN user_collectionus C ON u.CollectionID = c.ID
 where C.ID = 'AAA';

Как мне сделать это лучше или все хорошо?

В вашем коде довольно много проблем с именами. Вы можете подумать об установке расширения проверки орфографии в вашей среде IDE, чтобы помочь вам отловить их. Я ужасный орфограф, поэтому у меня есть дополнительные проверки орфографии везде (IDE, браузер и т. Д.)

Именование так важно, и самое главное, чтобы ваше именование применялось последовательно. В вашем db у вас есть PascalCase (UserName), а затем некоторые все нижние (phonenumber). Найдите тот, который вам нравится, и придерживайтесь его везде — он действительно поможет при чтении кода позже. Я не буду продолжать об этом, но я думаю, вам следует прочитать все свои имена еще раз и проверить, что они — лучший выбор.

К вашему SQL:

        string sql = @"SELECT  S.* ,  us.UserName, P.* , up.UserName , s.Price , 
         (s.Price /(  SELECT  COUNT(DISTINCT payer.ID) FROM payer  WHERE ShopID = s.ID  ) - p.Payed ) AS Topay
                       FROM
                           shop S
                         JOIN payer P ON S.ID = p.ShopID 
                         JOIN USER up ON p.UserID = up.ID
                         JOIN USER us ON s.UserID = us.ID
                                 where 
                                       us.CollectionID = @CollectionID ;";

Это необычное форматирование запроса.

• Почему USER верхний регистр?
• Облегчает ли способ написания сканирование / чтение?
• Почему большинство ключевых слов, но не все, написаны в верхнем регистре?
• Здорово, что вы используете параметры!

Не бойтесь переносов строк в SQL, они действительно улучшают читаемость:

   string sql = @"
SELECT  
    S.*,
    us.UserName,
    P.*,
    up.UserName,
    s.Price, 
    (s.Price / (SELECT COUNT(DISTINCT payer.ID)
                FROM payer 
                WHERE ShopID = s.ID) - p.Payed) AS Topay
FROM shop S
JOIN payer P 
    ON S.ID = p.ShopID 
JOIN user up
    ON p.UserID = up.ID
JOIN user us 
    ON s.UserID = us.ID
WHERE us.CollectionID = @CollectionID;";

Вы также можете изменить свои псевдонимы. up может быть payer_user а также us может быть shopper_user (просто примеры и оцените, хотите ли вы / нужно ли это делать).

Я не думаю, что этот запрос будет плохо масштабироваться, если у вас есть соответствующие индексы. Невозможно сказать, какими должны быть эти индексы, не имея возможности выполнить запрос с EXPLAIN. Я бы предположил, что Payer.ShopId был бы отличным кандидатом, так как он, вероятно, будет очень избирательным (как я предполагаю, есть несколько «плательщиков» на магазин). Как и все, что связано с производительностью, мера, мера, мера!

Обновлять:

Прочитав ваш комментарий о том, для чего предназначено приложение, я думаю, вы можете немного упростить себе жизнь. Предложение высокого уровня:

Покупка

• Идентификатор
• Покупатель (FK к таблице пользователей)
• … интересные сведения о покупке (например, payURL, дата и т. д.)

PurchaseSplit

• Должник (таблица FK to User)
• Покупка (таблица FK для покупок)
• Причитающаяся сумма
• Выплаченная сумма
• Id может быть просто (PurchaseId, UserId) или добавить столбец Id.

Пользователь

• Идентификатор
• Имя
• и т.п.

Когда кто-то совершает покупку, вы вставляете строку в Purchase и по одной строке для каждого «плательщика» в PurchaseSplit. При этом вы подсчитываете, какова доля затрат каждого «плательщика». Обратите внимание, что в вашем решении БД округление может привести к неправильной сумме, рассчитанной для всех.

После этого вы можете обновить PurchaseSplit после выплаты денег. Это простой компромисс — вы не получите истории платежей, но его легко реализовать и, вероятно, достаточно для вашего случая.

Если количество людей, разделяющих покупку, изменится, вы можете пересчитать сумму задолженности для всех «плательщиков» одновременно.

Ваш вопрос довольно сбивает с толку. Вы дважды присоединяетесь к USER table, но я не вижу для этого никаких причин.

Вот чего я не могу понять: (s.Price /( SELECT COUNT(DISTINCT payer.ID) FROM payer WHERE ShopID = s.ID ) - p.Payed ) AS Topay: делите цену на сумму payerс? Почему?

Честно говоря, я не могу понять вашу модель данных. Почему Shoping а также Payer есть UserName а также UserID? Почему у обоих есть Price? Что такое User_collection?

Находятся payerклиенты? Почему клиент должен быть привязан к магазину? Почему клиент не может покупать вещи более чем в одном магазине?

У вас есть бизнес-логика, которая выполняется при выполнении запроса. Это плохо, потому что оно блокирует ваше соединение с БД без всякой причины. Почему бы просто не получить нужные данные из БД, а затем работать с полученными данными за пределами using (IDbConnection cnn) блокировать?

Также похоже, что все это происходит внутри метода в контроллере вашего веб-API. Это плохо. Контроллеры должны быть компактными, вы должны переместить свою логику в определенные классы и использовать подобные MediatR чтобы подключить их к вашему контроллеру и / или внедрить CQRS.