SQL Representation of Arrays

[Charles_Kelley]

Hello, all!

I have a class that looks like this:

class FullAddress has   // sorry for the "has"; I use it in pseudocode to mean 'consisting of the following fields'.
	AddressID as integer
	BuildingName as string
	StreetAddress() as string
	Ward as string     // for my Japanese buddies
	City as string
	Province as string
	PostalCode as string
	Nation as string
end class // FullAddress

I can translate into SQL some of FullAddress, i.e.,

CREATE TABLE FullAddress
(
	AddressID INTEGER PRIMARY KEY
	BuildingName TEXT,
	Ward TEXT,
	City TEXT,
	Province TEXT,
	Nation TEXT,
	PostalCode TEXT,
)

But I am lost with StreetAddress(). Is there a way to translate such an array into SQL? I expect that I will have to limit StreetAddress to three lines, e.g.,

StreetAddressMain TEXT,   // the building number and street name, e.g., "10 Plateau Place"
StreetAddressUnit TEXT,   // the apartment number, e.g., "Apt. 6" and "Unit H"
StreetAddressExtra TEXT,  // in case there is extra information pertinent to the address

And do likewise to the class definition, i.e.,

class FullAddress has
	AddressID as integer
	BuildingName as string
	StreetAddressMain as string
	StreetAddressUnit as string
	StreetAddressExtra as string
	Ward as string
	City as string
	Province as string
	Nation as string
	PostalCode as string,
end class // FullAddress

My apologies for using the wrong Xojo terms and syntax.

Many thanks.

[Tim_Hare]

For something like an address, I just leave it as a single text field delimited by end of line. There’s really no need to separate it out into individual lines for either the database or the UI (use a textarea).

[Charles_Kelley]

Hello, Mr. Hare!

@Tim_Hare

For something like an address, I just leave it as a single text field delimited by end of line. There’s really no need to separate it out into individual lines for either the database or the UI (use a textarea).

Can a database store tab keys and end-of-lines?

[TimStreater]

Well, it’s just text, so yes, in principle.

Whether you should mix the data storage layer with a presentation layer is another matter. Once you have a database full of addresses containing tabs and newlines, you’re rather stuck with the layour it generates.

[Charles_Kelley]

Thank you for the confirmation, Mr. Streater.

@TimStreater

Well, it’s just text, so yes, in principle.

Whether you should mix the data storage layer with a presentation layer is another matter. Once you have a database full of addresses containing tabs and newlines, you’re rather stuck with the layour it generates.

[Rick_Araujo]

@Charles_Kelley

But I am lost with StreetAddress(). Is there a way to translate such an array into SQL? I expect that I will have to limit StreetAddress to three lines

People does not allow a “freestyle” address with any size and undefined number of lines, they usually think “what do I need, and what size limits, to get addresses that fits in a printable mailing label?” so they end with fields like address_main and address_complement, usually only 2 lines for this part.

[Kem_Tekinay]

I agree with the notion that you should use separate columns, not an array, for this purpose, but to answer your question as asked…

You didn’t specify the SQL engine you’re using. SQLite doesn’t have a concept of arrays*, but also doesn’t care what you store in any column. Your best bet is to declare the column as TEXT and translate the data to and from a JSON array.

*Someone correct me if I’m wrong please.

In other engines, e.g., PostgreSQL, you can declare a column as an array like so:

CREATE TABLE t ( arr TEXT[] );

But you still shouldn’t.

[Charles_Kelley]

Hello, Mr. Tekinay!

I agree with the notion that you should use separate columns, not an array, for this purpose, but to answer your question as asked…

You didn’t specify the SQL engine you’re using. SQLite doesn’t have a concept of arrays*, but also doesn’t care what you store in any column. Your best bet is to declare the column as TEXT and translate the data to and from a JSON array.

*Someone correct me if I’m wrong please.

In other engines, e.g., PostgreSQL, you can declare a column as an array like so:

CREATE TABLE t ( arr TEXT[] );

But you still shouldn’t.

Thank you for your comments. It’s an old project. I haven’t looked at it in five years or so and I think it’s time recompiling it for more recent systems. Since I’m going to all that trouble, so to speak, As long as I’m working on it, I ought to examine it to see what can be streamlined, how things that I used then are currently handled, etc.

By the way, it’s a SQLite database, but I’m thinking of converting it to PostgreSQL.

Thanks again for your remarks. I’ll take them under advisement.

[Charles_Kelley]

Hello, Mr. A.!

@Rick_Araujo

People does not allow a “freestyle” address with any size and undefined number of lines, they usually think “what do I need, and what size limits, to get addresses that fits in a printable mailing label?” so they end with fields like address_main and address_complement, usually only 2 lines for this part.

Thank you for your advisement.

Yes, I know. I have laid out mailing labels before, and I’m aware of their size limits. I probably will impose a limit of 20 characters a field.

Thank you again.

[Norman_Palardy]

a table with “lines” for the address would be one model

then youd have something like

CREATE TABLE FullAddress
(
	AddressID INTEGER PRIMARY KEY
	BuildingName TEXT,
	Ward TEXT,
	City TEXT,
	Province TEXT,
	Nation TEXT,
	PostalCode TEXT,
)

CREATE TABLE StreetAddressLines
(
        uid INTEGER PRIMARY KEY
	AddressID INTEGER ,
        lineID INTEGER,
        lineText TEXT
)

and the full address would be a join between the two tables on addressID and the street lines ordered by lineID

but really allyour doing is breaking the address into lines for a purpose I dont know

unless you have some specific reason to model it that way theres no compelling reason to that I can think of

I’m sure @Carol_J_Keeney will beat me up for that

[Rick_Araujo]

20 chars may be not enough. The most important part is not having “unlimited” lines, It usually never is more than 2 lines for any country.

[Charles_Kelley]

Hello, Mr. Palardy!

@Norman_Palardy

a table with “lines” for the address would be one model

then youd have something like

CREATE TABLE FullAddress
(
	AddressID INTEGER PRIMARY KEY
	BuildingName TEXT,
	Ward TEXT,
	City TEXT,
	Province TEXT,
	Nation TEXT,
	PostalCode TEXT,
)

CREATE TABLE StreetAddressLines
(
        uid INTEGER PRIMARY KEY
	AddressID INTEGER ,
        lineID INTEGER,
        lineText TEXT
)

and the full address would be a join between the two tables on addressID and the street lines ordered by lineID

but really allyour doing is breaking the address into lines for a purpose I dont know

unless you have some specific reason to model it that way theres no compelling reason to that I can think of

I’m sure @Carol_J_Keeney will beat me up for that

Thanks for the suggestion. I’m an information resource manager by profession, a.k.a., librarian. Based on my professional work, your model is the one I’m leaning towards. In the upgrade to my little database, I’ll probably develop it along that design. MARC (MAchine Readable Cataloging) cataloging incorporates UIDs in cataloging records mainly to provide common spellings to author names, ally variants in titles, etc.

It isn’t an easy model, to be sure, but information resource management is not an easy occupation because of all the variations in how information is presented, recorded, etc. Not like in the days of yore when all you had to track were books and journals.

Thanks for the confirmation.

[Charles_Kelley]

Hello again, Mr. A.!

@Rick_Araujo

20 chars may be not enough. The most important part is not having “unlimited” lines, It usually never is more than 2 lines for any country.

Well, I suppose the limit could be something be like 255 characters a line. Some fields could be filled in from a dropdown list. But my point is not to be too long that addresses on labels, letters, email, etc. cannot print without losing too much of the information.

Thanks again for your comments.

[Norman_Palardy]

printing is like viewing on screen and simply one form of output

and output should NOT drive how you model the data
they are separate concerns

[Charles_Kelley]

Hello again, Mr. Palardy!

@Norman_Palardy

printing is like viewing on screen and simply one form of output

and output should NOT drive how you model the data
they are separate concerns

Printing is not my main concern, but it is one of my concerns. My main concern is data storage and integrity. But I have other concerns: data entry and extraction, for example. I also have to consider printing or at least layout because of the needs of the organization for which I originally designed and wrote the application.

Thanks for your comments.

[Norman_Palardy]

Guess all I’m saying it properly modelling the data is one concern

Properly showing that data (on screen, on a printed label, etc) is a different concern

Proper modelling shouldnt be driven by how you expect to view the data
You wouldnt want to put font information, size etc etc IN the raw data itself simply because you expect to print something in bold
THey should be separated so the data can be used in whatever ways make sense and then the transformation process to an on screen view, web page, printed label can do just what it needs to do to make that data available and visible in that view
That might mean, with really long lines of text, to reduce the font size for a fix label size to make everything fit.
Or abbreviating common words like Road, Street, Avenue, etc

[Charles_Kelley]

@Norman_Palardy

Guess all I’m saying it properly modelling the data is one concern

Properly showing that data (on screen, on a printed label, etc) is a different concern

[etc., etc., etc.]

Yes, I know. I have modeled the data without recourse to font, style, size, etc. No librarian – I mean, information resource manager – worth tis salt would do so. I have written a separate application or rather group of routines to handle input and output.

Thanks for your comments. They have been most helpful.

[Carol_J_Keeney]

For the street address, it’s pretty standard internationally to allow up to three “lines” but the USPS standards just have the “Delivery Address Line”. (I’m using their “Publication 28” of their Postal Addressing Standards.) The software I work with primarily is used internationally and allows up to 3 “Street” lines (Street1, Street2, and Street3) with a length of 64. I’m okay with not splitting it out into a separate address line table.

[Charles_Kelley]

Hello, Ms. Keeney!

@Carol_J_Keeney

For the street address, it’s pretty standard internationally to allow up to three “lines” but the USPS standards just have the “Delivery Address Line”. (I’m using their “Publication 28” of their Postal Addressing Standards.) The software I work with primarily is used internationally and allows up to 3 “Street” lines (Street1, Street2, and Street3) with a length of 64. I’m okay with not splitting it out into a separate address line table.

I’m an idiot. I used to be the mailing list manager for my local ECK chapter, and I have a copy of the bulk-mail regulations. I forgot about it. Thanks for the reminder and the suggestion of 64 characters at three lines apiece. I think I used 50 characters at two lines apiece until one of our parishioners moved halfway across the world and we had to include the country.

Thanks again!