August 4, 2016 at 12:26 am
Hello,
I have about 100 tables which represents different entities related to person. For example, tables are person_car, person_children, person_house etc. So, in person_children are stored info about all children with data like first name, last name, birth date etc., in person_car is list of cars etc.
On the other side, I have table person_document, where documents are stored in binary field. Basically, user upload picture (scan) or PDF and code stores it in database in binary field.
Each document should be related to one row in one entity table. For example, scan of driving license is stored in person_document as binary, and that row should be somehow related to one row in person_driving_license table (person_driving_license has fields like license_date, jurisdiction, expiration_date etc.).
One row in document table belongs to one row in one entity table, but row in entity table could have multiple documents (e.g. current license scan and keep all previous scans as history).
Does anyone has idea how I could relate records from person_document to rows in each entity table?
I thought maybe to add entity_id in person_document, but how to define table name? Add one more field table_name in person_document? That sounds like I would need dynamic SQL then, to read table name and build query (can't have table name as variable in query).
Or, one more idea, to add new document_group table with only one field doc_group_id. Then add same doc_group_id field in person_document, and also in entity tables. So relationship could be:
entity_table.doc_group_id -> document_group.doc_group_id -> person_document.doc_group_id
Advantage on this way is that I would avoid defining of table name in fields, so query is simpler.
Please advice, did anyone work on similar case? How this problem is commonly resolved?
Thanks!
August 4, 2016 at 7:08 am
I can't speak for everybody but seeing a few of the tables definitions would go a LONG way in understanding what it is you are trying to accomplish here. I read this 2-3 times and kind of understand where you are going but not enough to offer any solid advice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2016 at 7:38 am
More questions than answers, I'm afraid, but...
Taking your example of the driving licence, why wouldn't the scan of the driving licence just be an attribute of the person_driving_licence table?
I see what you say about keeping history, but wouldn't a new scan of a driving licence replace an existing scan, or be a scan for a new licence (an therefore a separate row in the person_driving_licence table)?
Trying to model this in a documents table that can relate to more than one parent table is prone to errors, because you could end up, for example, relating a driving licence scan to a person_children row.
August 4, 2016 at 8:34 am
Sean Lange (8/4/2016)
I can't speak for everybody but seeing a few of the tables definitions would go a LONG way in understanding what it is you are trying to accomplish here. I read this 2-3 times and kind of understand where you are going but not enough to offer any solid advice.
Huh Sean, sorry for confusion. Thanks for the answer. It is a little bit complex, will try to clarify it.
Example entity table could be person_driving_license. So, person_driving_license has following fields:
license_id, person_id, license_number, license_type, jurisdiction, issuing_date, expiration_date
One person could have multiple driving licenses, for example one in USA and other taken in Germany. Or they could be of different type etc. So one row represents one license.
Then each license (each row), must be related to multiple documents (in this case that could be scanned image of current license and all previous expired scans).
Documents could be images or PDFs, and they are stored in document table in binary field.
So, how to relate documents to rows in entity tables? It would be simple for one table, but there are a lot of entity tables each for different topic (driving licenses, passports, children, real estate etc.). So, for example person_real_estate table has different fields than driving license table.
User should be able to get documents which belong to certain driving license or real estate property or passport etc.
August 4, 2016 at 8:44 am
Have you considered using SEQUENCE instead of an IDENTITY? Sequences are similar to identities, but they're not tied to a specific table.
There is another approach, but it's a lot more complicated and I would need the information that the others have requested to make a reasonable attempt at outlining it.
Drew
PS: It sounds like there are a great many design flaws in your database. For instance, take your person_child table. It has first_name, last_name, and birthdate. These are not attributes of the relationship, they are attributes of the child (violating 2nd normal form) and they belong on the child's record in the person table. (Yes, children are persons.)
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2016 at 8:46 am
Ian Scarlett (8/4/2016)
More questions than answers, I'm afraid, but...
It is a little harder, I try different approaches but they mostly look ugly for now. 🙂
Ian Scarlett (8/4/2016)
Taking your example of the driving licence, why wouldn't the scan of the driving licence just be an attribute of the person_driving_licence table?
Because multiple documents per row are needed. I could add a field if it is only one document per row. But, I need multiple documents for each row in person_driving_license.
Ian Scarlett (8/4/2016)
I see what you say about keeping history, but wouldn't a new scan of a driving licence replace an existing scan, or be a scan for a new licence (an therefore a separate row in the person_driving_licence table)?
It will be new scan for existing license. New scan cannot replace old scan, because the requirement is to keep all scans related to single row.
Ian Scarlett (8/4/2016)
Trying to model this in a documents table that can relate to more than one parent table is prone to errors, because you could end up, for example, relating a driving licence scan to a person_children row.
Indeed, only one ID field is not enough because we don't know the table name. Because of that I thought about additional table named document_group.
New document_group table would have just field doc_group_id. Then, same field will be in person_driving_license table and also all other entity tables. So, one row in person_driving_license table would be related to one row in document_group. Also, add this field to person_document table (where documents are stored).
So, to get documents for certain driving license with ID = 23, query could be
SELECT d.document_content FROM person_document d
INNER JOIN document_group g ON d.doc_group_id = g.doc_group_id
INNER JOIN person_driving_license dl ON g.doc_group_id = dl.doc_group_id
WHERE dl.ID = 23
August 4, 2016 at 8:52 am
drew.allen (8/4/2016)
Have you considered using SEQUENCE instead of an IDENTITY? Sequences are similar to identities, but they're not tied to a specific table.
Hey Drew, sequence is interesting idea. I didn't use it yet, so I will research a little about it.
drew.allen (8/4/2016)
There is another approach, but it's a lot more complicated and I would need the information that the others have requested to make a reasonable attempt at outlining it.
I am not sure how do you mean, but if I can help please let me know. I answered to previous posts already, please check.
drew.allen (8/4/2016)
PS: It sounds like there are a great many design flaws in your database. For instance, take your person_child table. It has first_name, last_name, and birthdate. These are not attributes of the relationship, they are attributes of the child (violating 2nd normal form) and they belong on the child's record in the person table. (Yes, children are persons.)
Children are persons indeed, I would not call that "great many design flaws", let's call it a little design flaws please. 🙂
August 4, 2016 at 8:58 am
Boris Pazin (8/4/2016)
drew.allen (8/4/2016)
There is another approach, but it's a lot more complicated and I would need the information that the others have requested to make a reasonable attempt at outlining it.I am not sure how do you mean, but if I can help please let me know. I answered to previous posts already, please check.
I mean table definitions and sample data as outlined in the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2016 at 9:14 am
Boris Pazin (8/4/2016)
Sean Lange (8/4/2016)
I can't speak for everybody but seeing a few of the tables definitions would go a LONG way in understanding what it is you are trying to accomplish here. I read this 2-3 times and kind of understand where you are going but not enough to offer any solid advice.Huh Sean, sorry for confusion. Thanks for the answer. It is a little bit complex, will try to clarify it.
Example entity table could be person_driving_license. So, person_driving_license has following fields:
license_id, person_id, license_number, license_type, jurisdiction, issuing_date, expiration_date
One person could have multiple driving licenses, for example one in USA and other taken in Germany. Or they could be of different type etc. So one row represents one license.
Then each license (each row), must be related to multiple documents (in this case that could be scanned image of current license and all previous expired scans).
Documents could be images or PDFs, and they are stored in document table in binary field.
So, how to relate documents to rows in entity tables? It would be simple for one table, but there are a lot of entity tables each for different topic (driving licenses, passports, children, real estate etc.). So, for example person_real_estate table has different fields than driving license table.
User should be able to get documents which belong to certain driving license or real estate property or passport etc.
Again...without the table definitions to help paint a clear picture of what you are trying to do this is just too difficult to provide a real answer. See the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2016 at 9:20 am
drew.allen (8/4/2016)
Boris Pazin (8/4/2016)
drew.allen (8/4/2016)
There is another approach, but it's a lot more complicated and I would need the information that the others have requested to make a reasonable attempt at outlining it.I am not sure how do you mean, but if I can help please let me know. I answered to previous posts already, please check.
I mean table definitions and sample data as outlined in the first link in my signature.
Drew
Here is the SQL:
CREATE TABLE #person_driving_license
(
license_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
person_id INT,
license_number VARCHAR(128),
license_type VARCHAR(50),
jurisdiction_id INT,
issuing_date DATETIME,
expiration_date DATETIME
)
CREATE TABLE #person_passport
(
passport_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
person_id INT,
passport_number VARCHAR(128),
country_id INT,
issuing_date DATETIME,
expiration_date DATETIME
)
CREATE TABLE #person_document
(
document_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
document_title VARCHAR(128),
document_type VARCHAR(20),
document_content [varbinary](max) NOT NULL,
insert_date DATETIME
)
SET IDENTITY_INSERT #person_driving_license ON
--===== Insert the test data into the test table
INSERT INTO #person_driving_license
(license_id, person_id, licence_number, license_type, jurisdiction_id, issuing_date, expiration_date)
SELECT 1, 1, 'first license number', 'first type', 1, 'Oct 17 2007 12:00AM','Oct 17 2012 12:00AM' UNION ALL
SELECT 1, 1, 'second license number', 'second type', 1, 'Oct 17 2010 12:00AM','Oct 17 2015 12:00AM'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #person_passport ON
INSERT INTO #person_passport
(passport_id, person_id, passport_number, country_id, issuing_date, expiration_date)
SELECT 1, 1, 'first passport number', 1, 'Oct 17 2012 12:00AM','Oct 17 2022 12:00AM'
For obvious reasons, I can't add query for inserting of binary data in person_document. But, let's imagine that we added 3 rows in person_document, and document_content field has some binary data. First 2 of these 3 documents must be related to "first license number" row in person_driving_license table. Last document should be related to "first passport number" in person_passport table.
August 4, 2016 at 11:46 am
There is no way that you can simplify joining to multiple entity tables. A join will be required for each entity table. The problem is that if all the entity tables have IDENTITY INT(1,1) sequences then there must be a way to know which table a document row should join to.
You have several ways to approach this. I'm sure I saw at least two mentioned above. (I'm throwing any others out just brainstorming. Please don't hesitate to point out flaws in any of them.)
1) You can assign a number to each class of entity (1 for children, 2 for cars, etc) and include that number as a column in the documents table. That number then becomes a part of the JOIN to the entity table it represents. You could use this number in a bridge table too, but there is no need for that unless you are describing many-to-many relationships.
2) You can use a SEQUENCE to assign unique numbers to all rows across entity tables. That way, if a car row has an ID of 12345, no child row will have that number, and vice versa.
3) Have a separate document table for each entity (CarDocs, ChildDocs, etc). If you need to bring all docs together, you can use a UNION ALL view.
4) Depending on how large you expect the tables to grow, you might assign a range to each entity table by means of your initial IDENTITY value and a constraint.
(Children 1-100,000,000, Cars 100,000,001 - 200,000,000).
If you are thinking about option 2 or 4 above, and your volumes are going to get very large you might consider using BIGENT for the key to each entity.
Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 4, 2016 at 12:07 pm
The Dixie Flatline (8/4/2016)
2) You can use a SEQUENCE to assign unique numbers to all rows across entity tables. That way, if a car row has an ID of 12345, no child row will have that number, and vice versa.
Thanks The Dixie Flatline for the list. At this moment, solution with SEQUENCE looks as right way to do it. I don't need any new table and also query will be very simple. For example to get driving license ID = 45:
SELECT d.document_content FROM person_document d
INNER JOIN person_driving_license dl ON d.entity_id = dl.entity_id
WHERE dl.license_ID = 45
Nothing hard in it, it is simple and efficient, can't see any flaw...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply