Unanswered: General database design question, arrays or separate form?
I am designing a Postgresql database (using pgAdmin III) to update a 20+ year old FoxPro program and I have what should be a few simple questions.
First, some columns in some fields may have multiple entries while others have only a single entry. Some of those columns may be interdependent, meaning the first entry in column A is matched with the first entry on column B and if there is not a matching entry in column B for each entry in column A the data is useless. Should I use arrays for these columns which may accept multiple entries (or may be empty) or should I create a separate form and just reference that with a foreign key?
Second, if I am referencing one form from another with a foreign key, what data type do I use in the corresponding column on the form making the reference to pull in (I assume) the primary key from the form being referenced?
In the case I'm talking about, for instance, I am logging a single trip with one point of origin and one destination, but I am tracking the miles I travel in each state. So while my form (Trip) is a single entry, this entry will contain several State and Miles entries which MUST match up to know how many miles I have traveled in a particular state, but only a single point of origin and (in this example, anyway) only a single destination. But I guess it gets a little more complicated than that as I may have multiple stops or "destination" on the trip, which may be all in one state or across several. So instead of a single destination and multiple state/miles entries I may have multiple destinations with a single state/miles entry.
Problems I've run into so far that I'm not quite sure to handle are, first, if I do a separate form for states and miles they STILL have to be arrays, but at least it's separated out to a single, simple form.
Second, the "Trip" form has (currently) a Primary Key, a Transaction number of type "Serial", a State array of a custom defined type with a list of the states and a Miles column of type "Numeric". If I want to pull a record from this form into another form I can do so by referencing (presumably) either the Primary Key (in which case I don't really need the transaction number) or the Transaction number. But when attempting to create a column to link to these foreign columns I don't know what data type to use. I'm not sure what data type I would need to set in my Trip form, for instance, to link to the Primary Key or Transaction number data in my State/Miles form.
It should be pretty straight forward, simple questions. Hopefully it's way less complicated than I'm making it instead of way more complicated than I realize. Thanks.