RE: Database Design Question
От | Gonzo Rock |
---|---|
Тема | RE: Database Design Question |
Дата | |
Msg-id | 3.0.5.32.20010727132144.009eae90@postoffice.pacbell.net обсуждение исходный текст |
Ответ на | Database Design Question (Gonzo Rock <GonzoRock@Excite.com>) |
Список | pgsql-sql |
OK... Fair Enough... Good Points indeed y'all. Well... What about the problem of users trying to Query the Database?? You know... like when using Crystal Reports or something?. SELECT * from HistoryTable WHERE PartID = SomeInteger vs SELECT * from HistoryTable WHERE PartNum = 12345636 AND PartRev = C How are they supposed to know What the PartID is ?? Anyway, that I why I was considering changing... current users always have trouble peering into the database... They don'tquite get it. At 02:31 PM 7/27/01 -0400, Mike Mascari wrote: >I prefer using unique integer ids generated from sequences rather than >keys composed of meaningful values. > >Advantages: > >Client side applications can store/handle the unique integer ids more >readily than having to deal with composite primary keys composed of >varying data types. For example, I can stuff the id associated with a >particular record easily in list boxes, combo boxes, edit controls, etc. >via SetItemData() or some other appropriate method. Its a bit more >complicated to track database records via composite keys of something >like: part no, vendor no, vendor group. > >Updating the data doesn't require cascading updates. If you use keys >with meaning, the referential integrity constraints must support >cascading updates so if the key changes in the primary table the change >is cascaded to all referencing tables as well. Earlier versions of most >databases (Access, Oracle, etc.) only provided cascading deletes under >the assumption you would be using sequence generated keys. > >Downside: > >Many queries might require more joins against the primary table to fetch >the relevant information associated with the numerical id, whereas keys >composed of solely the values with which they are associated might not >require the joins, which will speed some applications. I now have some >queries with 20-way joins. But PostgreSQL provides a way to explicitly >set the path the planner will choose and so the execution of the query >is instantaneous. I'm not sure about other databases. In earlier >versions, I had to denormalize a bit solely for performance reasons. > >In the past, I used to use composite keys and switched to the purely >sequence generated path and don't regret it at all. Of course, you'll >still have a unique constraint on the what-would-have-been meaningful >primary key. > >Hope that helps, > >Mike Mascari >mascarm@mascari.com > >Gonzo Rock wrote: >> >> A Question for those of you who consider yourself crack Database Designers. >> >> I am currently moving a large database(100+Tables) into pgSQL... with the intention of deploying against 'any' SQL databasein the future. The development side will be rigorously using Standard SQL constructs with no unique/proprietary extensions. >> >> My question concerns establishing the relationships. >> >> Currently Relationships between tables are established via a Unique Integer ID like this: >> >> *=APrimaryKey >> >> PartTypes Customer Parts >> --------- -------- ----- >> PartTypeID CustomerID PartID >> *PartType *Customer PartTypeID >> Address CustomerID >> *PartNumber(2FieldPrimaryKey) >> *PartRevision(2FieldPrimaryKey) >> PartName >> >> >> HOWEVER; I have read lots of texts describing the Relational Design should be instead like this: >> >> *=APrimaryKey >> >> PartTypes Customer Parts >> --------- -------- ----- >> *PartType *Customer PartType >> Address *PartNumber(2FieldPrimaryKey) >> *PartRevison(2FieldPrimaryKey) >> PartName >> Customer >> >> Both Techniques have a unique foreign key back to the parent tables but one uses No.Meaningful.Info.Integer.Data for theForeignKey while the second uses Human.Understandable.ForeignKeys >> >> Is one recommended over the other??? Sure appreciate the commentary before I get in too deep with all these tables. >> >> Thanks! >
В списке pgsql-sql по дате отправления: