Multiple references to same foreign table/column

  • Hello,

    For application easy and limitation of tables we have worked something out where we could for example have

    multiple addresses for one person/company,

    For this we created an little table in witch we record the addressid and the type of address

    but in other tables we could need multiple types of addresses witch lead u to multiple references and while this works perfect i still wonder whether this is considered a good practice or when bad , what the cost can be on long term,

    an example creation table where you can see multiple foreignkeys to the same table/column

    CREATE TABLE [dbo].[BASISREL](

    [BRL_ID] [int] IDENTITY(1,1) NOT NULL,

    [OFF_ID] [int] NULL,

    [MRL_ID] [int] NOT NULL,

    [PAR_ID_FIRMA] [int] NOT NULL,

    [PAR_ID_TYPE] [int] NOT NULL,

    [PAR_ID_DISC] [int] NOT NULL,

    [BRL_REGDATUM] [datetime] NULL,

    [BRL_BEGDATUM] [datetime] NULL,

    [BRL_ENDDATUM] [datetime] NULL,

    [BRL_REMOVED] [bit] NOT NULL CONSTRAINT [DF_BASISREL_BRL_REMOVED] DEFAULT ((0)),

    [BRL_BASISNR] [varchar](16) COLLATE Latin1_General_BIN NOT NULL,

    [ADR_ID_KLANT] [int] NOT NULL,

    [ARG_ID] [int] NULL,

    [ART_ID] [int] NULL,

    [ADR_ID_LAADNR] [int] NULL,

    [ADR_ID_LOSNR] [int] NULL,

    [PAR_ID_EENHEID] [int] NOT NULL,

    [PAR_ID_MUNT] [int] NOT NULL,

    [PLS_ID_LADEN] [int] NULL,

    [PLS_ID_LOSSEN] [int] NULL,

    [BRL_FACTLIJST] [int] NOT NULL CONSTRAINT [DF_BASISREL_BRL_FACTLIJST] DEFAULT ((0)),

    [BRL_OPM_FACTLIJST] [varchar](255) COLLATE Latin1_General_BIN NULL,

    [BRL_OPM_FACTUUR] [varchar](255) COLLATE Latin1_General_BIN NULL,

    [BRL_OPM_NED_LAAPLAATS] [varchar](255) COLLATE Latin1_General_BIN NULL,

    [BRL_OPM_FRA_LAADPLAATS] [varchar](255) COLLATE Latin1_General_BIN NULL,

    [BRL_OPM_NED_LOSPLAATS] [varchar](255) COLLATE Latin1_General_BIN NULL,

    [BRL_OPM_FRA_LOSPLAATS] [varchar](255) COLLATE Latin1_General_BIN NULL,

    [BRL_OPL_EIGENDOM] [int] NOT NULL CONSTRAINT [DF_BASISREL_PAR_ID_OPL_EIGENDOM] DEFAULT ((0)),

    CONSTRAINT [PK_BASISREL] PRIMARY KEY CLUSTERED

    (

    [BRL_ID] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],

    CONSTRAINT [UQ__BASISREL__0579B962] UNIQUE NONCLUSTERED

    (

    [BRL_ID] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    GO

    ALTER TABLE [dbo].[BASISREL] WITH CHECK ADD CONSTRAINT [FK_BASISREL_ADRES_KLANT_OP_ADRES_ADR_ID] FOREIGN KEY([ADR_ID_KLANT])

    REFERENCES [dbo].[ADRES] ([ADR_ID])

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Link tussen BASISRELATIE ADR_ID_KLANT en ADRES ADR_ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'BASISREL', @level2type=N'CONSTRAINT', @level2name=N'FK_BASISREL_ADRES_KLANT_OP_ADRES_ADR_ID'

    GO

    ALTER TABLE [dbo].[BASISREL] WITH CHECK ADD CONSTRAINT [FK_BASISREL_ADRES_OP_LAADNR_ADR_ID] FOREIGN KEY([ADR_ID_LAADNR])

    REFERENCES [dbo].[ADRES] ([ADR_ID])

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'relatie tussen Basisrelatie Laadplaats en ADRES' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'BASISREL', @level2type=N'CONSTRAINT', @level2name=N'FK_BASISREL_ADRES_OP_LAADNR_ADR_ID'

    GO

    ALTER TABLE [dbo].[BASISREL] WITH CHECK ADD CONSTRAINT [FK_BASISREL_ADRES_OP_LOSNR_ADR_ID] FOREIGN KEY([ADR_ID_LOSNR])

    REFERENCES [dbo].[ADRES] ([ADR_ID])

    GO

    Any feedback from expert users would be verry appreciated because i would like to know this things before the database becomes real big..

    Tnx,

    Eddy

  • Correctly normalizing this, your "little" table should reference the Entity the address goes with and the Address and Address Type. With this model, you would not need multiple Address references in your entity table. It would all be referenced through the small many-to-many table allowing an infinite number of addresses per entity without adding any fields.

    Now, although that it good practice for normalizing your database, in many cases it is overkill, so your design of multiple address id's in an entity table is not necessarily bad and can often lead to better performance. It just sacrifices the ability to add a new address type for a given entity without modifying the schema. Sometimes this is ok and you need to make the choice based on your business needs.

    One other comment I have. I am a big fan of useful table and column names. You may want to consider turning off your caps lock, not using underscores, and giving column and table names that have meaning.

  • Many-to-many in a separate table is the way to go on this. I've been through what you're proposing, and it works great for a few weeks, and then you will find yourself having to rebuild the table. Then again a few weeks after that. And so on, over and over. Many-to-many solution means build once, use forever.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Tnx for the reply Gquared and Earl,

    But the table i posted is what i would call an "end table", by witch i mean, its a collection of information thats frequently queried,

    In this you can see multiple references to a table called "ADRES"

    [ADR_ID_LAADNR] [int] NULL, point to an address for witch a location is stored where to load a truck

    [ADR_ID_LOSNR] [int] NULL, point to an address for witch a location is stored where to embarked a truck

    and so on,

    in fact al the addresses are already defined on type by another table in witch we can have 1 to n to address

    this because a customer can have a loading address, embarging address, financial address, customer address and so on.

    IN BASISREL (the posted table) is kept witch address to use for loading, embarking, financial, customer, etc..,

    Its not that there can only be one kind address but to point that out i will post tuesday the schema of the three tables.

    ADRSRT (address types, and active date ranges, blocking) Unique 1 side

    ADRESS (keeps all addresses)

    BASISREL (relationship table for customer, article, load and/or embargking places,..)

    ADRSRT (1-n) ADRES (1-n) BASISREL

    For naming convention :

    We are using a naming convention and the table names are useful : but we are dutch speaking

    BASISREL = Basis Relations

    ADRES = ADDRESSES (in dutch written as adres)

    [OFF_ID] [int] NULL, (first three chars are the table link : OFF -> table OFFERTE

    [MRL_ID] [int] NOT NULL, First three chars point to :MASTERRELATIE

    This way we can quickly see witch column is internal to the table and witch is external to the table (foreign relation)

    The one thing thats not my kind also are the CAPS, but the development is started based on a utility program for witch they have always used CAPS in tables and columns so my complains about it were lost in the water because the programmes would otherwise have to rewrite a great portion of the toolkit,

    But for stored procedures, views, diagrams etc, i use CamelLikeWriting eg: sproc_MyStoredProcedure because the toolkit has almost nothing to do with it.

    Hope this makes it a little bit more understandable what is ment.

    Wkr,

    Eddy

  • The idea of a many-to-many table is that you have one ID for the row in one table, one ID for the row in another table, and a many-to-many table in between to connect the two.

    So, for example, you have your BASISREL table, with BRL_ID. Don't put any address data in there. You have your ADRES table, with ADR_ID in it.

    Instead of having multiple columns in BASISREL, have a table called something like BASISREL_ADR, with column BRL_ID and ADR_ID, possibly a TYPE column as well.

    If you have a customer address for someone, they have a record in BASISREL, and the address in ADRES, and in BASISREL_ADDRESS, you have the two IDs. If they have one type of address, the join table would have one row. If they have five types of addresses, they would have five rows.

    If you need to keep a TYPE column in that table, you can do so, to indicate that it's a customer address, or a delivery address, or a load address, etc.

    If you do it with multiple columns in the same table, what happens when someone in management suddenly discovers a need for another type of address, maybe an alternate contact address, or a summer vacation address? What do you do if someone decides that you need to keep a historical record, like "old billing address", "current billing address"? What happens if a type of address is no longer needed?

    In the multiple columns method, you have to add more columns to the table, modify all the code that queries/inserts/updates that table.

    In the many-to-many method, you just insert another row into the join table, and you're done. Inserting a row is much simpler than adding a column to a table.

    This also avoids the issue of having a wide table with lots of null columns. What if you end up with five types of addresses, so you have five columns, but most customers only have one or two of those types? The table will still take up disk space, have less rows per page, etc. That means slower IO, more cache used for less data, etc. Multiple rows has none of those drawbacks.

    That's my advice on the thing. You don't have to take it, but it's the best advice I can give you on this situation.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply