I am running into a problem and need another set of eyes.
I am in the middle of building a database to support logins to my website (username & password)
There will be different roles with different persmissions.
Certain roles will have corresponding content in other tables.
For example I have a buyer table, I also have a buyer role.
Then I have a memberrep table that contains a record for anyone who can login to the system.
One table for all logins - no mater the role.
I need to connect a single buyer to a single login in the memberrep table.
I will have the same issue with the sellers and the salesreps. Each will have a one to one relation to a record in the memberep table.
Am I better off linking directly to the table without the 3rd table?
Or can I simply add the 3 or 4 columns into the memberrep table like buyerID, sellerID, salesrepID, ect.
And then only populate the ID of the login that relates to the external table?
I am at a loss here.. Is there another way to make this work?
Here is a screen shot of what I have already.
Any issues jump right out?
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.
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.
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.
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.
UserTable - with the additional field of RoleID
RoleTable - a list of all the roles available
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!
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..
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)
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...
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.
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
Last Visit: 31-Dec-99 18:00 Last Update: 23-Jul-21 5:53