You are designing your system in the incorrect order. You need to develop your business objects first. I know you are developing your DB first because you are trying to impose patterns on it that belong in your business objects.

You are also making the common mistake of thinking that there can only be one ID because you are thinking in terms of primary keys.

Also, it is not clear to me, that those things are as related as you think they are.

Assuming they are a related pattern, you should define interfaces to express that, IHasBusinessEntityId, ICreatedDate, IModifiedDate. Please do not forget the interface segregation principal. Then perhaps you can aggregate those interfaces into an IAuditable interface?

Explore the patterns in your business objects first, then you can concentrate on making your table structure fast and efficient.

Each of your tables can have the BusinessEntityId (perhaps a guid), then additionally, the individual tables sequential int identifier to use as the clustered index (guids are not great for clustered indexes). You can then use a SQL UNION to bring back all your IAuditable objects from disparate tables.

I would very much recommend against marrying all these to one table as it will cost you flexibility and performance problems.

This approach will result in clustered index fragmentation, which can drastically slow queries.

You are designing your system in the incorrect order. You need to develop your business objects first. I know you are developing your DB first because you are trying to impose patterns on it that belong in your business objects.

You are also making the common mistake of thinking that there can only be one ID because you are thinking in terms of primary keys.

Also, it is not clear to me, that those things are as related as you think they are.

Assuming they are a related pattern, you should define interfaces to express that, IHasBusinessEntityId, ICreatedDate, IModifiedDate. Please do not forget the interface segregation principal. Then perhaps you can aggregate those interfaces into an IAuditable interface?

Explore the patterns in your business objects first, then you can concentrate on making your table structure fast and efficient.

Each of your tables can have the BusinessEntityId (perhaps a guid, maybe ints created by a SEQUENCE), then additionally, the individual tables sequential int identifier to use as the clustered index (guids are not great for clustered indexes). You can then use a SQL UNION to bring back all your IAuditable objects from disparate tables.

I would very much recommend against marrying all these to one table as it will cost you flexibility and performance problems.

This approach will result in clustered index fragmentation, which can drastically slow queries.

You are designing your system in the incorrect order. You need to develop your business objects first. I know you are developing your DB first because you are trying to impose patterns on it that belong in your business objects.

You are also making the common mistake of thinking that there can only be one ID because you are thinking in terms of primary keys.

Also, it is not clear to me, that those things are as related as you think they are.

Assuming they are a related pattern, you should define interfaces to express that, IHasBusinessEntityId, ICreatedDate, IModifiedDate. Please do not forget the interface segregation principal. Then perhaps you can aggregate them into a IAuditable interface?

Explore the patterns in your business objects first, then you can concentrate on making your table structure fast and efficient.

Each of your tables can have the BusinessEntityId (perhaps a guid), then additionally, the individual tables sequential int identifier to use as the clustered index (guids are not great for clustered indexes). You can then use a SQL UNION to bring back all your IAuditable objects from disparate tables.

I would very much recommend against marrying all these in one table as it will cost you flexibility and performance problems.

You are designing your system in the incorrect order. You need to develop your business objects first. I know you are developing your DB first because you are trying to impose patterns on it that belong in your business objects.

You are also making the common mistake of thinking that there can only be one ID because you are thinking in terms of primary keys.

Also, it is not clear to me, that those things are as related as you think they are.

Assuming they are a related pattern, you should define interfaces to express that, IHasBusinessEntityId, ICreatedDate, IModifiedDate. Please do not forget the interface segregation principal. Then perhaps you can aggregate those interfaces into an IAuditable interface?

Explore the patterns in your business objects first, then you can concentrate on making your table structure fast and efficient.

Each of your tables can have the BusinessEntityId (perhaps a guid), then additionally, the individual tables sequential int identifier to use as the clustered index (guids are not great for clustered indexes). You can then use a SQL UNION to bring back all your IAuditable objects from disparate tables.

I would very much recommend against marrying all these to one table as it will cost you flexibility and performance problems.

This approach will result in clustered index fragmentation, which can drastically slow queries.