Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Amethystium
Aged Yak Warrior
701 Posts |
 Posted - 2003-07-10 : 10:34:36
|
| Hi all,This is a problem which I have mentioned here before, but unfortunately I think most people misunderstood what I am trying to achieve. Anyway, I am going to try and explain exactly what I want to achieve with this mini project.I have been working on a Pension Administration software for quite some time, mainly doing data migration. One of the problems which I constantly face is tracing a particular members data in the database.For example, say I have a the National ID Number of a perticualr member. It would be nice to know which tables hold information for this particular number. Now you're probably thinking why I don't just run some queries to find out. The answer is quite simple : There are 418 tables!What I am trying to develop is some sort of utility that would take in a national ID number and return all possible tables which hold a record for that member.Here is a simple part of the software structure... what we have isPerson -- hold personal informationPersonUIDAddressParentuid - which is the same as the PersonuidBankAccountParentuid -- same as the PersonuidDivorcePerosnUIDPensionShareDivorceUIDIf a member has a record in Address, BankAccount, Divorce and PensionShare, how do I design some sort of structure to return those rows?You see, it is easy to establish if a person has a record in Address because we just get the PersonUID from person where the NationalIDNumber = 'The One' and join it on Address where the ParentUID = PersonUID.Again the same can be done on the other tables, but the problem occur when we come to the Divorce table. How do I design a structure so that it goes through the tables associated with each table checked?Any design tips would be appreciated.---------------Shadow to Light |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-10 : 11:01:48
|
| Do the # of tables which have a "PersonUID" column change repeatedly without your knowledge? If not, then a view (admittedly quite a beefy one) might be what you're after.The general problem with this abstract approach is the column lists, which will differ from table to table. No matter how you structure a solution, you will need to enforce a common set of columns in each of these 418 tables to return, or not return any columns from the tables at all.Jonathan{0} |
 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-07-10 : 11:22:48
|
I think one of the Admins here told me to scrap my project because I am defeating the object of relational databases if you know what I mean.The problem as I said is knowing which table holds data for a prticular member.One thing I did not explain is the following :The person table is really the main table in the database. Every member must have a person table which contains details such as forname, surname, dateofbirth, sex etc..A person whill also have an employee record which contains a information such as DateJoinedCompany, EmployeeReference etc..An Employee will always be part of a pension scheme, therefore will have a record in the SchemeMember table. A schemeMember will always have a benefit so (s)he will have a MemberBenefit Record or more depending on how many benefits they have joined.I would say that 70% of the information stored in the database link back to the Unique key in the Memberbenefit Table. The other 30% are divided between Person, Employee, SchemeMember.So following my original post, the person table (let's call it a Parent Table) will have Address, BankAccount and Notes as child tables. These tables will never have a record linking them to the Employee table. They will always contain PersonUIDs. Does that make sense?Person ---> Parent Address ---> Child of Person BankAccount ---> Child of Person Notes ---> Child of Person Divorce ---> Child of Person Earmarked ---> Child of Divorce PensionShare ---> Child of Divorce PensionShareCharges ---> Child of PensionShareSo, if you give a nationalIDNumber, we start off withSET @PERSONUID = (SELECT PERSONUID FROM PERSON WHERE NATIONALIDNUMBER = 'D45387A') we thenSELECT PARENTUID FROM ADDRESS WHERE PARENTUID = @PERSONUIDSELECT PARENTUID FROM BANKACCOUNT WHERE PARENTUID = @PERSONUID etc...Of course I can write 418 select statments but that is why I want to develop a new tool that would do the job for me.Something like a table containing what I call ParentTablesand another table called child tables.I hope I am making sense and not driving you crazy with my explanations! ---------------Shadow to Light |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-10 : 11:31:21
|
quote:
The problem as I said is knowing which table holds data for a prticular member.
Run a query against information_schema.columns to see.quote:
SELECT PARENTUID FROM ADDRESS WHERE PARENTUID = @PERSONUIDSELECT PARENTUID FROM BANKACCOUNT WHERE PARENTUID = @PERSONUID...
And what do you do intend to do with this? What columns do you want out of tables like address and bankaccount?quote:
I would say that 70% of the information stored in the database link back to the Unique key in the Memberbenefit Table. The other 30% are divided between Person, Employee, SchemeMember. So following my original post, the person table (let's call it a Parent Table) will have Address, BankAccount and Notes as child tables. These tables will never have a record linking them to the Employee table. They will always contain PersonUIDs. Does that make sense?
Not really ...  . Honestly, I don't see that the why matters here so much since it's already done. I think the bigger question is how you intend to retrieve data from these 418 tables once you somehow decide that they have data for a particular person ID.Jonathan{0} |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-07-10 : 11:51:51
|
quote:
Not really ... . Honestly, I don't see that the why matters here so much since it's already done. I think the bigger question is how you intend to retrieve data from these 418 tables once you somehow decide that they have data for a particular person ID.
You see, the idea is not which records I want to retrieve. That will depend on what I am trying to fix for this member. The problem occurs when I get an Admin giving a particular members Surname or NationalIDNumber. It would be nice to have a utility where you enter the NationalIDNumber and it returns a list of all the tables that hold data for this member. Note that I am not interetsed in which fields it returns, just the tablename.Something like :Get PersonUID where the nationalIDNumber = 'xxxxxxx'BEGIN LOOPIf ParentUID in address = PersonUIDAdd to TABLES ('Address')If ParentUID in BankAccount = PersonUIDAdd to TABLES ('BankAccount')The PersonUID is only ever used in 9 tables out of 400+When we are finished with the tables asscoiated with PersonUID we then turn our attention to the EmployeeTable.Again the Employee table is a child of Person but Employee has children tables of its own too. For example the Employee table has a table contributionHistory etc..PERSON[PERSONUID][FORNAME][SURNAME]....EMPLOYEE[EMPLOYEEUID][PERSONUID][DATEJOINEDCOMPANY]...ADDRESS[PARENTUID][ADDRESS1][ADDRESS2]...CONTRIBUTIONHISTORY[PARENTUID][DATERECEIVED]....As you can see, both the ContributionHistory table and Address have Parentuid but as I said, Only PersonUIDs will ever be found in the Address table and only EmployeeUIDs will be found in the ContributionHistory table.So the utility would start off at Person table, check ALL tables associated with the Person table. When it's finished, it starts with Employee and searches through all the tables associated with the Employee table. When it's finished, it moves on to the SchemeMember table and seraches for records in the tables associated with the Schememember table etc..When it's finished, it should return all the tablenames where this member has a link to.---------------Shadow to Light |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-10 : 11:59:08
|
quote:
It would be nice to have a utility where you enter the NationalIDNumber and it returns a list of all the tables that hold data for this member. Note that I am not interetsed in which fields it returns, just the tablename
There it is! You want just the tablename.But ...Your columns are named poorly. What you need is a view that normalizes the 'UID' column to one name, along with the table name. Then you can declare a cursor against it and run your queries, checking for a rowcount > 0.Edited by - setbasedisthetruepath on 07/10/2003 12:01:14 |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-07-10 : 12:15:36
|
quote:
But ...Your columns are named poorly. What you need is a view that normalizes the 'UID' column to one name, along with the table name. Then you can declare a cursor against it and run your queries, checking for a rowcount > 0.
Yes!! I think we're on the same wavelength now. Are you saying that the guys who developed the software named the columns poorly? ouch!I know exactly what you mean...Have a look at this :SELECT CAST(NULL AS INTEGER) AS TABLEID, SO.NAME AS TABLENAME, SC.NAME AS COLUMNNAME INTO OPENCOLUMNSFROM AVON_TEST..SYSCOLUMNS SC, AVON_TEST..SYSOBJECTS SOWHERE SO.ID=SC.IDAND SO.XTYPE='U'AND SC.NAME LIKE '%UID'AND SC.XTYPE = 56ORDER BY SO.NAMEGO this makes sure that only columns containing keys are pulled out along with their table name.Looks like I am on the right track!Thanks for the help.Regards.---------------Shadow to Light |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-10 : 12:31:35
|
| I'm not really knocking the data modeling, just so you know. But I never like structures similar to:Person ( personID int, ... )Employee ( employeeID int, ... )Janitor ( janitorID int, ... )when in fact they are all person ID's. Naming them according to their subtype is incorrect data modeling.Jonathan{0} |
|
|
|
|
|
|
|
|
Design Issue