[SQL] one table being used for two purposes with foreign key for each?
What if, like Amazon, a customer can have more than one address on file? Then you make "addresses" a separate table with one-to-many relationship. So then you're making affiliates (or clients, or distributors) and you realize it would be nice to re-use the fields you already have there in the "addresses" table, even though the affiliate's address is one-to-one, NOT a one-to-many. Would it be bad design to have different foreign keys, as shown below? Is this a bad idea? Should I just re-type the address field definitions directly into the affiliates table, since it's not one-to-many? create table customers ( id serial primary key, name varchar(64) ); create table affiliates ( id serial primary key, name varchar(64) ); create table addresses ( id serial primary key, customer_id int REFERENCES customers(id), affiliate_id int REFERENCES affiliates(id), addr1 varchar(64), addr2 varchar(64), city varchar(64), state varchar(12), postalcode varchar(12), country char(62), CONSTRAINT needs_link CHECK (customer_id IS NOT NULL OR affiliate_id IS NOT NULL) ); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Formatting an Interval
On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote: > > > All of the functions that i've looked at seem to only extract parts > > from Intervals. What I need to do is to format the interval. For > > instance, I have a Interval with the value 1 day 07:57:52. I would > > like that in HH:MM:SS. So in the example the output would be > > 31:57:52. > > I'm not aware of a built-in way to get such a format; somebody > please correct me if I'm mistaken. > > You could write a function to format the interval. For example, > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > convert the interval to a number of seconds; convert that to hours, > minutes, and seconds; and use TO_CHAR to format the return value. to_char() works with standard date/time ranges, for example 1-24 -- so there is no way how convert to anything like "31:57:52". Karel -- Karel Zak <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Formatting an Interval
On Sun, Jan 02, 2005 at 05:19:23PM +0100, Karel Zak wrote: > On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > > > You could write a function to format the interval. For example, > > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > > convert the interval to a number of seconds; convert that to hours, > > minutes, and seconds; and use TO_CHAR to format the return value. > > to_char() works with standard date/time ranges, for example 1-24 -- so > there is no way how convert to anything like "31:57:52". TO_CHAR() works with several types, including integer, numeric, and double precision. If you've broken the interval into three variables, one containing hours, one containing minutes, and one containing seconds, then you can use TO_CHAR() to add leading zeroes where needed. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Formatting an Interval
am Sun, dem 02.01.2005, um 17:19:23 +0100 mailte Karel Zak folgendes: > > You could write a function to format the interval. For example, > > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > > convert the interval to a number of seconds; convert that to hours, > > minutes, and seconds; and use TO_CHAR to format the return value. > > to_char() works with standard date/time ranges, for example 1-24 -- so > there is no way how convert to anything like "31:57:52". Read again. Extract the seconds and calculate the hours, minutes and seconds. test_db=# select extract (epoch from '1 day 07:57:52'::interval); date_part --- 115072 (1 Zeile) Okay, 115072 Seconds. test_db=# select extract (day from '1 day 07:57:52'::interval); date_part --- 1 (1 Zeile) Okay, this is 1 day, 24 hours. test_db=# select extract (epoch from '1 day 07:57:52'::interval) - 60*60*24*(extract (day from '1 day 07:57:52'::interval)); ?column? -- 28672 (1 Zeile) Okay, 24 hours and 28672 seconds, and you know, this is less then 1 day. Now calculate, how many hours in 28672 seconds: test_db=# select 28672 / 3600; ?column? -- 7 (1 Zeile) Now you can add 24 hours and 7 hours, the remainder is test_db=# select 28672 - 3600*7; ?column? -- 3472 (1 Zeile) seconds. Now you can calculate the minutes and, finaly, the seconds. Is the way now okay? Write a function for this job. Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Formatting an Interval
Note that there will be a loss of precision as an interval of 1 month, for instance, does not mean any specific number of days, as : 1 february + 1 month = 1 march (1 month = 28 or 29 days) 1 december + 1 month = 1 january(1 month = 31 days) Same for years etc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
