0

I am a bit confused while designing my table structure for managing addresses and contacts for multiple entities .

In my case I have four types of entity which would have multiple address and contact .

I have created 4 parent table for 4 entities . But while creating the address and contacts table , I am thinking of creating just one address and one contact table for for all parent entities and link them through a Entity Key .some thing like below .

Id
ContactType
ParentId
EntityKey
Value

So is it a good idea or should I create 4 individual child tables each for one type of entity . All my child tables would have the same structure . Please suggest me . I am not sure which design to follow .

Deb
  • 391
  • 2
  • 20

2 Answers2

0

Case 1 : millions of data in no time.query is frequently use and it is use in multiple places.There may be change in table structure of any parent-child. then

multiple parent and multiple child Reason : When table grow so rapidly,then you do partition on table.Then why not keep separate table from the beginning.Also you will have one less where condition.

Case 2 :When growth of table is normal.and query use on it is in very few places,and concurrency is less.then you can have One Parent and One Child table also

------------ According the URL reference--------- I am telling you exactly same,where

Entity=Address table with other details and AddressTypeID and ParentID I am only saying no need of further normalisation like creating Table for AddressType and referencing with AddressTypeID.Since AddressTypeID can be maximum upto 4-5.And while insert you can pass hard coded value like 1,2

Say i want to join Address table with Entity1 then my query

From Address A inner join Entity1 E on A.ParentID=P.EntityID1 where AddressTypeID =1

similarly

From Address A inner join Entity2 E on A.ParentID=P.EntityID2 where AddressTypeID =2

and so on.

Suppose in another example when you can have n number of type in AddressType then you are bound to create separate table like in that url

KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • o k . In my case concurrency would be very less as this data wouldn't be used frequently . But I see a problem as I am using entity framework I always need to add a where clause in my query . I can't create direct foreign key relationship here if i go for multiple parent and one child – Deb Dec 28 '15 at 08:47
  • i don't believe,you can't create many to one relation in EF.But it is safe to create one extra column called AddressType int.t'row you may need to filter all address of one type,without joining. – KumarHarsh Dec 28 '15 at 11:03
0

I created all my parent tables and one respective child table for address and one for contact . I didn't create any relation between parent and child entities . I made the relation through one more table where I did the cross reference between all parent entities and child entity . I added a check constraint which will check only one parent entity id is filled and other parent entity id columns are null .I also added a unique index to avoid duplicate entries . In this way I can also maintain the referential integrit .

Deb
  • 391
  • 2
  • 20
  • "I added a check constraint which will check only one parent entity id is filled and other parent entity id columns are null ." this is not clear.can you explain with example.draw all tables.Creating one mapping table is terrific idea,it just slip out of my mind,while discussing. – KumarHarsh Dec 30 '15 at 03:47
  • Please check the database diagram how I have related the tables . If I am doing wrong please suggest me . I have the option to change my database structure . In EntityToAddress mapping table I have added all parentIds as a column and linked them to their respective parent tables and allowed null values . Adding a check constraints makes sure that I have one address id mapped to only one parent id and other parents id columns are null . Please find the db diagram in another answer . – Deb Dec 30 '15 at 04:16