Share your knowledge at the LQ Wiki.
Go Back > Forums > Linux Forums > Linux - General
User Name
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.


  Search this Thread
Old 05-25-2009, 11:46 AM   #1
LQ Newbie
Registered: Apr 2009
Posts: 29

Rep: Reputation: 15
PostgreSQL database design?

I am setting up a three level LINUX Virtual Server, where;

Level 1: Two Virtual Servers (Primary & Backup) passing queries to Real servers,
Level 2: Two (or more) Real servers running CentOS 5.3, Postfix, freeRADIUS, PostgreSQL Client, Apache, NFS Client, etc., sharing data on Data Servers (see Level 3),
Level 3: One Data Server (more later on) running CentOS 5.3, PostgreSQL Server & NFS Server.

Data Server only connects to the Real Servers and has no direct Internet connection. I have two questions:

I plan to have three type of data on the Data Server; (a) Customer files/data, such as customers name, address, phone#, UID,P/W, etc., (b) Postfix files/data, such as emails, etc., & (c) freeRADIUS files/data, such as login, connections, usage, etc.

All three data needs to be linked (keys) with one and another. I hope you get the picture.

My question is about setting files/data for these three applications. I read in the PostgreSQL documentation that a role (user) canít access data in more than one database. In my case, these three files/data will have some common links with one another and the queries might retrieve data from one or more of these dataís.

For Example: Postfix data has UID & P/W and freeRADIUS has the same. Both of these are linked (?). A query might get the user name from the customer data, display emails in the queue. (this is just an example)

I am trying to figure out best way to design the data layout. One design come to my mind is to setup one database and three schemas. This way I can have data for each application located separately in each schema but in the same database. Therefore the data tables can be accessed altogether if needed.

I want your expertise to let me know if I am at right track or should follow a different design.

I am not very much experienced with PostgreSQL but know enough to work in it. So please donít use difficult lingo but respond in simple English.

In the above example, I want to store UID/PW in the PostgreSQL. Is there a document which describes how is it done or do I have to write a routine do accomplish this?

Example: When I add new customers, some information goes in the customer tables, some in the Postfix tables and there is a link between the data for the same customer.

Once the UID/PW are stored in the PostgreSQL, how to setup Postfix & freeRADIUS to lookup this data to authenticate? In the past I have used MD5 to store UID/PW. This is a new challenge for me!

Jennifer K.

PS: I had hard time describing my questions. English is my 2nd (or 3rd) language, so forgive the poor English.
Old 05-26-2009, 09:27 AM   #2
Senior Member
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541

Rep: Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060
When I design a data base I put the most effort into what information is going into the tables that make up the data base; for example, if I am dealing with marketing products, I will have a table that contains unique customer information (customer number, name, address, contact information), a table that contains unique product information (product number, vendor number, description, price, price effective date, weight), a table that contains customer orders (order number, customer number, product number, units, date), a vendor table (vendor number, name, address, contact information). There would be additional tables (customer credit card(s) information, customer shipping address if different than billing, things like that). The idea is that every table contains only what it has to and nothing more; there is no sense whatsoever in retrieving product descriptions from one table and storing them in another table, for example.

Obviously, this is minimal -- there would be more tables (our orders from vendors might be an important one) but the basic idea is keep it a simple as possible because we'll use the data base management system (DBMS) to do the work for us. A simple rule of thumb is that you never store the same data in more than one table. The rows in the customer order table would only contain the order number, customer number, product number, quantity, date and nothing more (there would be one or more rows determined by the number of products ordered). You might want to add a column to the order table that contains the shipping date for that product or you might have a separate table for that depending upon your circumstances.

You're going to insert and retrieve information from these tables with joins, on customer number, on product number, on vendor number, on date and the like; that's what a DBMS does for you (where table_a.customer_number = table_b.customer_number and the like). It really does not matter too much what DBMS you use, PostgreSQL, MySQL, Informix, Oracle and others all do the same thing in slightly different ways; it's up to you to make the DBMS' job easier by keeping things simple and as small as possible (if you've got a table that's got 128 columns in it, you haven't thought it through).

When you're defining column data types, try to use the defaults (date, integer, float, double); the DBMS knows how to dealing with those on your platform, let it. Do not ever store dates as strings; there's a date data type for a reason, use it. Do not be tempted to use small integer, define integers as integer (you're not wasting space worth worrying about and a small integer will come back and bite you when you overflow it -- and you will). Use numeric values for customer numbers, product numbers and the like; numeric comparisons are much, much faster than strings -- if you want to have a vendor part number (say, ISBN for books), add that as a column after the numeric product number and just don't display your internal product number.

Permissions are granted at the DBMS level on tables and individual columns in tables to users. You need to think through who has what kind of access to which tables and what columns within those tables and put effort in granting (and perhaps revoking) permissions. Your customers will need to create an account, add and update their credit card information, add and cancel orders. Your people will need to update an order when it ships, all that sort of thing. It's not trivial and needs a good amount of attention. User identification numbers and passwords are usually at the DBMS level, not at a table level; you can create a log table that records who connected (this is where the datetime data type comes in handy) and disconnected (ditto) or your DBMS may have a logging capability that will do that for you (I do not know if PostgreSQL does).

Anyway, best of luck with it!

Hope this helps some.
1 members found this post helpful.


database, postgresql

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to better design a database Jzarecta Linux - General 1 02-10-2007 02:48 AM
Database Design Help msound Programming 7 12-11-2006 05:51 PM
Database design Wim Sturkenboom Programming 4 08-30-2006 02:45 AM
More database design issues logicdisaster Programming 7 08-11-2004 05:52 PM
Database design issues logicdisaster Programming 6 08-07-2004 11:52 AM > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 07:18 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration