database is normalized but the resulting keys are a problem

views:

134

answers:

+1 Q:

database is normalized but the resulting keys are a problem

but the prob is there are relations ships which are so huge that after normalizing they have like a 20 primary keys( composite keys) which are really foreign keys

but have to be declared as primary keys to identify the relationship uniquely. so please help? is it correct

and i apologize to the expert community for not accepting answers, i was not aware that accepting is possible, the TICK MARK is that visible :-)

It sounds like your database is huge and has a lot of relationships; one thing you can do to simplify the primary key situation is to define a single column as the primary key for each table, and use an automatically incremented int or guid datatype. That way you can ensure uniqueness and your foreign keys are at least independent of your primary key.

Guy Starbuck 2010-05-17 17:55:35

users is not a solution i like to use but its the only which fits my needs, the argument made by other users are correct, but as per my client requirement i cannot change the structure( the existence) of the table in question.thank you

2010-05-22 12:32:15

I'm having trouble visualizing a design with 20 tables that have a relationship to one table.

I can't tell without looking at your data design, but it sounds like you designed a hierarchical database, rather than a relational database.

Gilbert Le Blanc 2010-05-17 17:58:05

No, happens. I had a system once with 200 tables referencing one. CMS (Web CMS) and one table was "ContentItem" that held the hierarchy of items. Pretty much everything "was a content item", including shop offers, prices in them, security, users etc.

TomTom 2010-05-18 14:02:24

@TomTom: Yes, ContentItem is a look up table. But to have a table that's in a relationship with 20 other tables, that has a foreign key with 20 other tables, that's unusual. I can't visualize a design like that.

Gilbert Le Blanc 2010-05-18 14:28:08

object hierarchy- looked identical. Actually not so hard to work with with an O/R mapper- pretty much everything inherited from ContentItem. Now I have a similar thing in a financial application. Table: ops.Item - OperationsItem. On this table we handle security, so many many different tables have a key that points to "their" ops.Item (accounts etc. - even users, for who can edit a user etc.).

TomTom 2010-05-18 15:03:06

@TomTom: I'm having a hard time visualizing your financial example. It's probably my limitations as a data modeler.

Gilbert Le Blanc 2010-05-18 15:23:07

Well, imagine you have a security system like windows has (permissions, users, granted permissions) and various objects that can have rights. At the end you have a table "SecuredObject" that - basically - will have a LOT of objects point to it. Because you need that table to model the permissions on those objects.

TomTom 2010-05-18 15:51:37

+3 A:

If you say that you have "foreign keys that really are foreign keys but that need to be declared as primary keys", then you actually indicate that you lack the competence and the skill and the authority to be doing database design.

Foreign keys and "primary" keys are quite distinct concepts, which are quite impossible to confuse with one another by anyone who is even just remotely knowledgeable in the field of database design.

Maybe you could give it a second try to explain what it actually is that you mean.

Erwin Smout 2010-05-17 20:14:17

+1 - nice to see "factually correct" instead of "politically correct".

TomTom 2010-05-18 14:00:36

+2 A:

First, do not use composite keys ever. They are a bad technique. They are slow and are a nightmare to maintain when they change.

If you need uniqueness over two or more fields, you do not need a primary key, you need a unique index. Make the PK of the table a surrogate key (preferably int).

If you are trying to create tables with a one-to-one relationship, it is acceptable to use the PK of the parent table as the PK of the Child table and set a PK_FK relationship between the tables; however it would be unusual to need 20 separate one-to-one tables.

HLGEM 2010-05-17 21:37:04

@HLGEM, your advice is contradictory isn't it? By suggesting that you need a "unique index" I understand you to mean that the composite key constraint should be enforced in that way. Therefore that it is still a de-facto composite key. There's nothing wrong with composite keys - they are a good thing. But in my opinion they are better if created explicitly by unique constraints (SQL UNIQUE or PRIMARY KEY constraints for example) rather than implicitly through indexes. That's assuming the DBMS in question supports unique constraints.

dportas 2010-05-18 13:38:11

A unique index is not a composite key. It is not used to relate to other tables and thus is not a KEY at all. The reason why you don't want composite keys is that in relating to other tables you need to have the smallest key possible for performance. You also need something whose values will never change and that is extremly rare with composite keys. I would never consider using a database that didn't allow unique indexes, that would be just a toy not a real database.

HLGEM 2010-05-18 13:43:33

A key is not just something that is "used to relate to other tables". A candidate key is a set of column attributes that is required to be unique and is irreducibly so. A composite key is any candidate key with more than one attribute. Composite keys are therefore essential for effective database design. Whether any such key is referenced in another table is a different question. Also, it's quite possible for a composite key to be smaller than a single attribute key. Size is certainly a factor but it's not the only one and is rarely the most important.

dportas 2010-05-18 14:18:52

ansaurus

tags:

views:

answers:

database is normalized but the resulting keys are a problem

related questions