Database

FirstPrevNext

Re: design questions - tables to support users Luc Pattyn27-Aug-09 10:57 Luc Pattyn 27-Aug-09 10:57  Hi, I'm not a DB expert, but your drawing looks way too complex. I see a lot of duplicate field names, which to me is typical for a poor design. For starters, I would have a PERSON table, holding a person ID, and all info regarding that person, independent of his role(s); so fname, lname, phone, email, etc. Then, I would have a PERMISSIONS table, holding an role ID, a role name, and all the permissions; not sure whether I would choose many permission columns, or use several rows with only one permission field (and when in doubt several rows/1 column often is the right choice). And finally I would have a ROLES table, holding a person ID and a role ID. If a person has multiple roles, just give him multiple rows. As a result, in order to determine whether person X has permission Y, you would search person X and permission Y in a join of all three tables. If you find one or more rows that match, the permission is granted. Hope this helps. Luc Pattyn Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you. Re: design questions - tables to support users Kimmmmy27-Aug-09 12:04 Kimmmmy 27-Aug-09 12:04  Thanks for the reply. I am confusing myself - believe me. I have a working user/role table setup that is here - the memberrep table is your person table. I've used this table design for many websites. [^] Independent of the login tables -(and this is where I get confused explaining what has to happen) I have several other 'data' tables. A buyer , who will be a buyer and have a login. I want to relate the buyer to his user login. I want to do the same with a seller (another similar but different table) and yet a 3rd, salesrep. In order for my buyer to login to the admin and see his data in the buyer table, I need to relate his buyer record to his login record. I am considering adding the memberRepID to the buyer table - and then I will have the relation. But duplicate data (fname,lname,email,etc) It's like it's calling me to create seperate login tables for each external 'content' type, The buyer, seller and salesrep tables are not tables I am using to define the role. does that make sense? Re: design questions - tables to support users Luc Pattyn27-Aug-09 12:29 Luc Pattyn 27-Aug-09 12:29  Kimmmmy wrote: does that make sense? I couldn't tell, you've lost me early on. I suggest you start from the hard data, that to me is PERSONS. And I really would insist on a very neutral name for that table, it has nothing to do with functions, roles, permissions, which are all weakly defined and volatile (they can evolve in all kinds of ways), non-unique, and overlapping. IMO in general, more tables is wrong except for normalization (i.e. avoiding duplicate entries in fields). Think of it like this: using more tables will result in needing more code, and does it really improve the quality or the power of your system? Here is a functional question: does a person with two roles also use two logins? if yes (I hope not), the login belongs somewhat to the role; if not, the login belongs to the person, and may or may not be one or a few fields in the PERSONS table; I would be inclined to keep it apart, just personID and login data. I wish an expert kicked in here. I would learn too. Luc Pattyn Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you. Re: design questions - tables to support users Kimmmmy27-Aug-09 12:38 Kimmmmy 27-Aug-09 12:38  Yes, a person with two roles would have one login. Although in this site, I do not see that happening. (famous last words) I appreciate your help. I need to do some more research. Thank you for your time. Re: design questions - tables to support users Mycroft Holmes27-Aug-09 14:34 Mycroft Holmes 27-Aug-09 14:34  Didn't look at the graphic but this is a simple issue, you need to answer 1 question. Can a user have more that 1 role. Yes UserTable - all the details of the user as per Luc's suggestion RoleTable - a list of all the roles available LinkTable - a many to many link table with userid and roleid When the user logs in they select the role to be used, you have a chicken/egg issue here - how do I filter the possible roles bfore the user has logged in, you don't you validate the role after the user and return for a role selection is an incorrect one is requested. No UserTable - with the additional field of RoleID RoleTable - a list of all the roles available Validate the user only, role is automatic. Re: design questions - tables to support users Luc Pattyn27-Aug-09 15:25 Luc Pattyn 27-Aug-09 15:25  Seems we fully agree, your description is much better though. You had the advantage of not having seen the diagram! Luc Pattyn Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you. Re: design questions - tables to support users Mycroft Holmes27-Aug-09 16:04 Mycroft Holmes 27-Aug-09 16:04  So for once I can thank my net nazi for not allowing image sharing sites problem:storing rtf formatted data into sql askhar51627-Aug-09 2:30 askhar516 27-Aug-09 2:30  Hi,all,may be this is a simple question, but i can't fix it. i can't store rtf format of any unicode string or chinese string into sql database by using sql statement. The unicode string or chinese string is from the richtextbox, i used "rtfData=RichTextBox.Rtf" and tried to insert rtfData int sql database, but i can't. is there any special character i have to deal with in the rtf formatted data?please help,thanks! Re: problem:storing rtf formatted data into sql εїзεїзεїз2-Sep-09 13:53 εїзεїзεїз 2-Sep-09 13:53  please can you explain on a little more that on front end you are using any application like vb , vc or what and which approach are you using to store the data in data base data set , objects or what Best Of Regards, SOFTDEV Sad like books with torn pages, sad like unfinished stories ... Re: problem:storing rtf formatted data into sql askhar5162-Sep-09 17:12 askhar516 2-Sep-09 17:12  thanks for the reply,i used the vb.net ,i fixed the problem by doubling the single quote. parent and child interbase query peter83126-Aug-09 20:28 peter831 26-Aug-09 20:28  hello there, can anyone plz tel me how to write a query to fetch all names of parents, children and subchildren in interbase? ex: School class1 21students class2 24students school2 class1 30students... plz i have it as a task at work plz help me thank you. Re: parent and child interbase query SeMartens26-Aug-09 20:38 SeMartens 26-Aug-09 20:38  Hi, I didn't get this. What is your table, how do the columns look like? That might be helpful to solve your problem. Regards Sebastian It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland. Re: parent and child interbase query peter83126-Aug-09 21:04 peter831 26-Aug-09 21:04  well dear sebastian i have to display the table names in my ibexpert this is my first time trying to fetch table names and not fields using standard queries. i have to display in the 1st column the parent tables names and next to them in the second columns the children and the same for the subchildren for each parent.. Re: parent and child interbase query SeMartens26-Aug-09 21:09 SeMartens 26-Aug-09 21:09  Still didn't get this. Could you give an example? It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland. Re: parent and child interbase query peter83126-Aug-09 21:19 peter831 26-Aug-09 21:19  mm.. lets see how to express this, i have a parent table called (types) in it i have many children and sub children tables. i need to display them in a query where in the 1st column the table types should appear then in the second column the children of that table should appear and in the 3rd column the children of the children tables should appears. ex: types(1st column) --------sales_unp(2nd column) --------sales ------------stock_flow(3rd column) ------------invoices_unp ------------invoices --------unordered_items_hdr(2nd column) ------------unordered_items(3rd column) ... Re: parent and child interbase query SeMartens26-Aug-09 21:25 SeMartens 26-Aug-09 21:25  So do you need a JOIN? Like: Select * from (types inner join sales on types.id = sales.typeid) inner join ... It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland. Re: parent and child interbase query peter83126-Aug-09 21:37 peter831 26-Aug-09 21:37  to be honest i dont know what i need ... its logical to use joins if i want the children of tables to appear but to be honest its an alien query to me... i feel so weak in queries but ofcourse i cant join all the tables there r so many! there must be a way to cite them all and display them in such way or else my boss wouldnt have told me to do so... Re: parent and child interbase query SeMartens26-Aug-09 21:43 SeMartens 26-Aug-09 21:43  Don't be shy about this. We were all beginners once Have a look at different tutorials about joins: http://www.w3schools.com/Sql/sql_join.asp[^] http://www.sql-tutorial.net/SQL-JOIN.asp[^] Joins about more than two table are nothing special, I know queries including up to twenty tables. Regards Sebastian It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland. Re: parent and child interbase query peter83126-Aug-09 21:42 peter831 26-Aug-09 21:42  the SELECT * from table... would display the fields in that table.. this is not what i need. i need the table names only. not the fields in them. Re: parent and child interbase query SeMartens26-Aug-09 21:45 SeMartens 26-Aug-09 21:45  Ok, we are getting close. Normally DBMS store the information about tables in so-called system tables. For SQL Server you can get them by using the query "SELECT * FROM sys.tables"... http://msdn.microsoft.com/en-us/library/aa260604(SQL.80).aspx[^] It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland. Re: parent and child interbase query peter83126-Aug-09 21:52 peter831 26-Aug-09 21:52  THANK GOD WE ARE UNDERSTANDING EACH OTHER.. look the interbase (ibexpert) doesnt use sys.anything there must be some sort of other kywords for such database... this is my main issue. i have allready found sys.datatable_name...and stuff but the sys is not understood by ibexpert.. i tried to google it ... i found an answer in experts exchange.com but in order to view the answer i had to pay! and i dont have any credit cards on me... to be honest no matter if u could help me or not i am so thankful for your support! so thankful for taking time one me like this. Re: parent and child interbase query SeMartens26-Aug-09 22:04 SeMartens 26-Aug-09 22:04  Scroll down to the bottom of the page (experts exchange). After all the advertising there will be the solution (don't know why they want to pay for this...). It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland. Re: parent and child interbase query peter83126-Aug-09 22:13 peter831 26-Aug-09 22:13  i cant belive the result was under my nose all the time and i didnt see it! thank you sebastian... ill never forget your support! if u got curious ... the thing i wanted is (you type in google fetch parent child tables interbase) you will get the title article (fetch parent child... uppercased u enter it and you will find what i wanted exactly..) thank you so much. hop this works.becos it says oracle ... thank you again Sql query problem adkalavadia26-Aug-09 20:09 adkalavadia 26-Aug-09 20:09  id person bar 1 ankur royal 2 ankur wishy 3 punit royal 4 punit wishy 5 ankit royal result will be Ankur punit i want to list person who use more than one bar.how can i do...i want sql query for ms access. thank in advance... !- F - R - I - E - N - D - S -! Re: Sql query problem i.j.russell26-Aug-09 21:46 i.j.russell 26-Aug-09 21:46  SELECT person FROM [tablename] GROUP BY Person HAVING COUNT(bar) > 1

Last Visit: 31-Dec-99 18:00     Last Update: 24-Jun-24 8:31 Refresh ᐊ Prev1...776777778779780781782783784785 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.