任何人都可以建议我的表格结构满足以下类型的要求吗? [英] Can anyone suggest me a table structure for the following type of requirement?
问题描述
我有一个要求,例如为每个用户分配一些公司分支机构位置,例如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屋!