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:

Q1:
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.

Q2:
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.

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.