任何人都可以建议我的表格结构满足以下类型的要求吗? [英] Can anyone suggest me a table structure for the following type of requirement?

查看:51
本文介绍了任何人都可以建议我的表格结构满足以下类型的要求吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个要求,例如为每个用户分配一些公司分支机构位置,例如A,B,C,并且还分配了account_type,例如D,E,F,G,H。有一个名为客户代码的表,其中包含customer_code名称,代码等以及可能是A或B或C或All的分支以及可能是D或E或F或G或H或ALL的帐户类型。所以我的目标是当用户1登录时,他应该只能查看客户代码,与分支(A或B或C)和account_type(D或E或F或G或H或ALL)相关联的名称。 />


我有一个表格结构,如





 User_branch_act_type 

-------------------------------------- -------------
Sr-no |用户名|分公司|帐户类型|
---------------------------------------------- -----
1 |用户1 | A | F,H |
1 |用户2 | C | D,E.F |
1 |用户3 | A,B,C | E,H |
1 |用户4 | B,C | G,E |
---------------------------------------------- -----

客户代码

--------------------------- -----------------------------------------------
Sr_no |客户名称|客户代码|分公司| Account_Type |
---------------------------------------------- ----------------------------
1 |客户1 | 566 | B | D |
1 |客户2 | 545 | A | G |
1 |客户3 | 512 | C | E |
1 |客户4 | 589 | A | H |
1 |客户5 | 537 | B | F |
---------------------------------------------- ----------------------------



所以例如用户1搜索客户的代码只应被允许搜索其分支机构为客户2和客户4的客户,同时他应该只能查看账户类型为F或H的客户代码。因此用户1应该能够获得客户4的客户代码。



任何建议表示赞赏。表结构是否足以检索数据,还是应该修改它?在此先感谢。

解决方案

通常避免使用逗号将数据编码到列中,创建详细信息表,这样可以让您的生活更轻松,查询也更简单。


这是多对多关系的问题。例如,您可以使用两个外键创建单独的表user-branch associations,一个引用用户,另一个引用 - 分支。关于帐户类型的相同事项:表用户帐户类型关联。



-SA


< BLOCKQUOTE> 1。从'user_branch_act_type'表中删除帐户类型

2.使用Sr_no和帐户类型创建另一个表'user_account',形成复合键

table:user_account

Sr_no,主键

账户类型,主键

3.将2个表连接在一起通过Sr_no

4 。通过account_type字段将'user_account'表链接到'customer codes'表



对分支列执行相同操作。


I have a requirement such as every user is assigned some company branch location such as A,B,C and is also assigned account_type such as D,E,F,G,H. There is a table named customer codes which contains the customer_code name, code etc along with the branch which maybe A or B or C or All and account type which may be D or E or F or G or H or ALL. So my goal is that when USER 1 logs in he should only be able to view the customer codes, name associated with branch (A or B or C) and account_type (D or E or F or G or H or ALL).

I have a table structure in mind such as


User_branch_act_type

---------------------------------------------------
Sr-no   |   Username  |  Branch  |  Account Type  |
---------------------------------------------------
1       |   User 1    |  A       |   F,H          |
1       |   User 2    |  C       |   D,E.F        |
1       |   User 3    |  A,B,C   |   E,H          |
1       |   User 4    |  B,C     |   G,E          |
---------------------------------------------------

Customer Codes

--------------------------------------------------------------------------
Sr_no   |  Customer Name  |  Customer Codes  |  Branch  |  Account_Type  |
--------------------------------------------------------------------------
1       |  Customer 1     |   566            |  B       |  D             |
1       |  Customer 2     |   545            |  A       |  G             |
1       |  Customer 3     |   512            |  C       |  E             |
1       |  Customer 4     |   589            |  A       |  H             |
1       |  Customer 5     |   537            |  B       |  F             |
--------------------------------------------------------------------------


So for instance User 1 searches for the code of the customer he should only be allowed to search for the customers whose branch is A i.e Customer 2 and Customer 4 and at the same time he should only be able to view the customer codes whose account type is F or H. Therefore User 1 should be able to get the customer code of Customer 4.

Any advice is appreciated. Is the table structure feasible enough to retrieve the data or should I modify it? Thanks in advance.

解决方案

Generally avoid encoding data with commas into columns, create a details table instead this will make you life easier and queries much simpler.


This is a problem of many-to-many relation. For example, you can create a separate table "user-branch associations" with two foreign keys, one referencing a user, another one — a branch. Same thing about account types: a table "user-account type associations".

—SA


1. Remove the account type from the 'user_branch_act_type' table
2. Create another table say 'user_account' like this with Sr_no and Account type forming the composite key
table: user_account
Sr_no, primary key
Account Type, primary key
3. link the 2 tables together thru Sr_no
4. link 'user_account' table to 'customer codes' table thru account_type fields

Do the same for branch column.


这篇关于任何人都可以建议我的表格结构满足以下类型的要求吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆