Mam takie tabele:
CREATE TABLE `psAdmin` ( `id_admin` int(10) UNSIGNED NOT NULL, `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `company_id` int(11) NOT NULL, `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(128) COLLATE utf8_unicode_ci NOT NULL, `salt` varchar(128) COLLATE utf8_unicode_ci NOT NULL, `last_logged_in_date` datetime NOT NULL, `valid_account_in_time` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `expiration_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `psAdminDetails` ( `id_admin_details` int(10) UNSIGNED NOT NULL, `id_admin` int(10) UNSIGNED NOT NULL, `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL, `company_name` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL, `nip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `street` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `post_code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `country_id` int(11) NOT NULL DEFAULT '0', `provincial_id` int(11) NOT NULL DEFAULT '0', `avatar` varchar(65) COLLATE utf8_unicode_ci DEFAULT NULL, `level` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `notes` text COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `psAdminLoginAttempts` ( `id_admin_login_attempts` int(10) UNSIGNED NOT NULL, `id_admin` int(10) UNSIGNED NOT NULL, `time` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `psAdminLoginHistory` ( `id_admin_history` int(10) UNSIGNED NOT NULL, `id_admin` int(10) UNSIGNED NOT NULL, `time` datetime NOT NULL, `ip` varchar(25) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `psCompany` ( `id_company` int(10) UNSIGNED NOT NULL, `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `name` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL, `nip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `notes` text COLLATE utf8_unicode_ci, `street` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `post_code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `country_id` int(11) NOT NULL DEFAULT '0', `provincial_id` int(11) NOT NULL DEFAULT '0', `contact_person` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `www` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `phone_number` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL, `date_of_registration` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `psProvincial` ( `provincial_id` int(10) UNSIGNED NOT NULL, `name` varchar(35) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `psAdmin` ADD PRIMARY KEY (`id_admin`); ALTER TABLE `psAdminDetails` ADD PRIMARY KEY (`id_admin_details`); ALTER TABLE `psAdminLoginAttempts` ADD PRIMARY KEY (`id_admin_login_attempts`); ALTER TABLE `psAdminLoginHistory` ADD PRIMARY KEY (`id_admin_history`); ALTER TABLE `psCompany` ADD PRIMARY KEY (`id_company`); ALTER TABLE `psProvincial` ADD PRIMARY KEY (`provincial_id`); ALTER TABLE `psAdmin` MODIFY `id_admin` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE `psAdminDetails` MODIFY `id_admin_details` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE `psAdminLoginAttempts` MODIFY `id_admin_login_attempts` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE `psAdminLoginHistory` MODIFY `id_admin_history` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE `psCompany` MODIFY `id_company` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE `psProvincial` MODIFY `provincial_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; COMMIT;
tabele mają następujące funkcje:
- psAdmin - tabela loginów administratorów
- psAdminDetails - tabela danych szczegółowych administratorów
- psAdminLoginAttempts - tabela z próbami logowania administratorów
- psAdminLoginHistory - tabela z historią logowania administratorów
- psCompany - baza firm. Firm może być nieskończenie wiele. Do każdej firmy można przypisać administratorów
- psProvincial - województwa
Czy dodalibyście jeszcze jakieś klucze (np. obce)? Jeśli tak, to gdzie?
Jest sens łączyć:
1. psAdminLoginAttempts.id_admin = psAdmin.id_admin
2. psAdminLoginHistory.id_admin = psAdmin.id_admin
3. psAdmin.company_id = psCompany.id_company
4. psAdminDetails.id_admin = psAdmin.id_admin
5. psAdminDetails.provincial_id = psAdmin.id_admin
6. psAdminLoginAttempts.id_admin = psAdmin.id_admin
7. psAdminLoginHistory.id_admin = psAdmin.id_admin
kluczami obcymi?