Table which can reference a number of other tables

Hi everyone,

I have a question regarding database design, I hope that this is
appropriate for the list. Let's say that I have the following tables:

clients (id,name)
contacts (id, name, phone, client_id (FK))

companies (id, name)
employees (id, name, phone, company_id (FK))

logins (id, username, password)


What's the best way to connect contacts and employees to the logins
table? I've thought of duplicating the username & password fields
into both the contacts and employees tables, adding both contact_id
and employee_id foreign keys to the logins table, and adding login_id
foreign keys to the contacts and employees tables, but none of these
solutions seem very smart.

Thanks,
Marc

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Table which can reference a number of other tables

Hi Marc,

- If you use this database for some website, the solution is:

clients (id,name)
contacts (id, name, phone, client_id (FK), username, password)

companies (id, name)
employees (id, name, phone, company_id (FK), username, password)

logins table will be removed

Then you must build web interface for each group (in this example is interface for contacts and another for employees).

- Another solution is:

logins (id, username, password, group_type, user_id)
group_type: contacts, employees, ...
user_id: contact_id or employee_id
If group_type is contacts, then query to the contacts table with some contact_id
If group_type is employees, then query to the employees table with some employee_id

----- Original Message -----
From: "Marc Guay"
To: mysql@lists.mysql.com
Sent: Thursday, July 22, 2010 12:44:17 AM
Subject: Table which can reference a number of other tables

Hi everyone,

I have a question regarding database design, I hope that this is
appropriate for the list. Let's say that I have the following tables:

clients (id,name)
contacts (id, name, phone, client_id (FK))

companies (id, name)
employees (id, name, phone, company_id (FK))

logins (id, username, password)


What's the best way to connect contacts and employees to the logins
table? I've thought of duplicating the username & password fields
into both the contacts and employees tables, adding both contact_id
and employee_id foreign keys to the logins table, and adding login_id
foreign keys to the contacts and employees tables, but none of these
solutions seem very smart.

Thanks,
Marc

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=cuong.manh@vienthongso.co m


--
Best Regards,
Cuongmc.

--
Nguyen Manh Cuong
Phong Ky Thuat - Cong ty Vien Thong So - VTC
Dien thoai: 0912051542
Gmail : philipscuong@gmail.com
YahooMail : philipscuong@yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Table which can reference a number of other tables

On 7/21/2010 1:44 PM, Marc Guay wrote:
> Hi everyone,
>
> I have a question regarding database design, I hope that this is
> appropriate for the list. Let's say that I have the following tables:
>
> clients (id,name)
> contacts (id, name, phone, client_id (FK))
>
> companies (id, name)
> employees (id, name, phone, company_id (FK))
>
> logins (id, username, password)
>
>
> What's the best way to connect contacts and employees to the logins
> table? I've thought of duplicating the username & password fields
> into both the contacts and employees tables, adding both contact_id
> and employee_id foreign keys to the logins table, and adding login_id
> foreign keys to the contacts and employees tables, but none of these
> solutions seem very smart.
>

Normalization rules still apply.

If everyone is a LOGIN, that is your top-most table.
If a CLIENT is a LOGIN with a NAME added to it, then `clients` becomes
(id, login_id (fk), name)
If a contact is a CLIENT with a phone number added to it, then
`contacts` becomes
(id, client_id (fk), phone)

In theory, that will give you accurate storage with the least
duplication. In practice, however, you can absolutely "over-normalize"
data to the point where all you do is write JOIN queries and it takes
forever to get information out of the database.

So the trick is to find the right balance between theory and performance.

In practice, look at what actions you need and how you can segregate
your data based on how the data is used. If you need to search BOTH
lists of people (clients and contacts) frequently, then the normalized
approach works just fine (all names are in one table). If you do not,
the segregating that data into two tables may work out better.

Careful de-normalization can result in speed improvements. However I
suggest strongly that all data designs should start normalized and only
de-normalize where the physical components of performance indicate the
need to do so.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Table which can reference a number of other tables

I keep running into problems like this and have another example of it
that might be clearer.

I have 4 tables, Newsletters, Contacts, Industries, and Contact Groups.

We send Newsletters to Contacts, either grouped by Industry or Contact Group.

Contact Groups must be associated with an Industry. Contacts must be
associated with an Industry, but not necessarily a Contact Group.

For example, sometimes we would like to send a Newsletter to all
Contacts who are in the real estate Industry, and sometimes, we only
want to send newsletters to Contacts who are members of the Planet
Earth Real Estate Board. So far, I have the following:

Newsletters
------------------
id
content
contact_group_id (optional)
industry_id (optional)

Contacts
------------------
id
name
email
industry_id (FK)
contact_group_id (FK) (optional)

Industries
------------------
id
name

Contact Groups
------------------
id
name
industry_id (FK)


Are suggested solutions for this problem any different from those I've
received for the previous example? The real problem is when a table
(Newsletters) can be associated with 2 or more other tables (Contact
Group or Industry).

Thanks for any guidance.
Marc

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Table which can reference a number of other tables

--0016361e883220b7b0048c4f9723
Content-Type: text/plain; charset=ISO-8859-1

Am I correct in assuming that "Industries" can be seen as some sort of
"super contact-group" in your application?
If so, you could merge "Contact group" and "Industries" --> "Contact group"

Contact Groups
------------------
id
name
parent_id (FK)
type ("industry_level, "sub_level", "some_other_level")

this would enable a tree of nested contact-groups, where your top contact
group is the "Industry group" as you mentioned.

Then Contacts would become:

Contacts
------------------
id
name
email
contact_group_id(FK) (the lowest level group a contact belongs to)

this would enable you to fetch contacts:
1. for some low-level group, e.g.:
select * from contacts as c, group as g1 where c.contact_group_id=g1.id and
g1.name='some group'

2. for industry as well when industry is one level above contact group if
you're still following, e.g.:
select * from contacts as c, group as g1, group as g2 where
c.contact_group_id=g1.id and g1.parentid=g2.id and g2.name='some industry'

you can see it becomes rather unelegant for higher levels
(g1.parentid=g2.id and
g2.parentid=g3.id.... g(n-1).parentid=gn.id....

Alternatively, you could create a NxN lookup table between contacts and
contactgroups, like:

Contacts
------------------
id
name
email

Contact Groups
------------------
id
name
type ("industry_level, "sub_level", "some_other_level")

ContactToGroupTable
---------------------------
id (autoinc)
contactid (FK)
contactgroupid (FK)

and on creation of a contact insert all relations (lowest contact group and
up the tree until you reach Industry-level) as seperate rows in
ContactToGroupTable.

Now you have abstracted away from difference between Industry and Contact
Group (Again, I assume that's how you use Industry) , and you're able to
have an aggregated group-in-group structure as deep as you like on which ou
can do queries like:

select c.* from contacts as c, contactgroup as g, contactToGroupTable as x
where g.name='some contactgroup or some industry' and g
.id=x.contactgroupid
and x.contactid=c .id

HTH,
Geert-Jan

2010/7/26 Marc Guay

> I keep running into problems like this and have another example of it
> that might be clearer.
>
> I have 4 tables, Newsletters, Contacts, Industries, and Contact Groups.
>
> We send Newsletters to Contacts, either grouped by Industry or Contact
> Group.
>
> Contact Groups must be associated with an Industry. Contacts must be
> associated with an Industry, but not necessarily a Contact Group.
>
> For example, sometimes we would like to send a Newsletter to all
> Contacts who are in the real estate Industry, and sometimes, we only
> want to send newsletters to Contacts who are members of the Planet
> Earth Real Estate Board. So far, I have the following:
>
> Newsletters
> ------------------
> id
> content
> contact_group_id (optional)
> industry_id (optional)
>
> Contacts
> ------------------
> id
> name
> email
> industry_id (FK)
> contact_group_id (FK) (optional)
>
> Industries
> ------------------
> id
> name
>
> Contact Groups
> ------------------
> id
> name
> industry_id (FK)
>
>
> Are suggested solutions for this problem any different from those I've
> received for the previous example? The real problem is when a table
> (Newsletters) can be associated with 2 or more other tables (Contact
> Group or Industry).
>
> Thanks for any guidance.
> Marc
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gbrits@gmail.com
>
>

--0016361e883220b7b0048c4f9723--

RESET MASTER doesn"t work

--_000_C889AAA9E2F4jhammermanvideoeggcom_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello MySQL-users list,

We are having an issue with replication and binlog files and I was hoping t=
hat this list could shed a little light on the issue for us. We have a pair=
of 5.1.41-community MySQL servers. We attempted to restore from the slave =
server, and in the process I zeroed out the mysql-bin.index file with the e=
cho command (I know this makes me a terrible person; in my own defense, thi=
s was effective technique for restoration on on 5.0 MySQL machines).

Now, when we run:

mysql> show master status;

We see

+------------------+-----------+--------------+------------- -----+
| Fil| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------- -----+
| mysql-bin.000121 | 429017611 | | |
+------------------+-----------+--------------+------------- -----+

But if we run

mysql> SHOW BINARY LOGS;

We see

Empty set (0.00 sec)

And the RESET MASTER command has no effect.

We saw on http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.htm l

Prior to MySQL 5.0.60, PURGE BINARY LOGS TO and PURGE BINARY LOGS BEFORE di=
d not behave in the same way (and neither one behaved correctly) when binar=
y log files listed in the .index file had been removed from the system by s=
ome other means (such as using rm on Linux). Beginning with MySQL 5.0.60, b=
oth variants of the statement fail with an error in such cases. (Bug#18199 =
, Bug#18453 om/bug.php?id=3D18453> ) To handle such errors, edit the .index file (which=
is a simple text file) manually to ensure that it lists only the binary lo=
g files that are actually present, then run again the PURGE BINARY LOGS rge-binary-logs.html> statement that failed.

However running PURGE BINARY LOGS against a file listed in the mysql-bin.in=
dex file (once this file has been updated to reflefc all of the logs contai=
ned within it's directory) still results in

ERROR 1373: Target log not found in binlog index

Can anyone shed some light on this issue for us?

Thanks!

--_000_C889AAA9E2F4jhammermanvideoeggcom_--