在mysql中为不同类型的数据类型使用不同的表

I'm creating a database for a member administration. We've got elements (users) and these users have properties (for instance, surname, last name, date of birth). Now, the names are text, but the date of birth is a date, so i want to store this in a column with datatype "date". Because the properties of the users may differ, i want a table with elements, a table with properties and a table that links the two together. However, to be able to use different kind of datatypes, for each datatype i would have to create a different table. You would get something like this:

我正在为成员管理创建一个数据库。我们有元素(用户),这些用户有属性(例如,姓氏,姓氏,出生日期)。现在,名称是文本,但出生日期是一个日期,所以我想将其存储在数据类型为“date”的列中。因为用户的属性可能不同,我想要一个包含元素的表,一个包含属性的表和一个将两者链接在一起的表。但是,为了能够使用不同类型的数据类型,对于每种数据类型,我必须创建一个不同的表。你会得到这样的东西:

Table: Users
------------
user
1
2

table: properties
---------------------------------
property  propertyName   datatype
1         surname        text
2         lastname       text
3         date of birth  date

Table: PropertiesString
---------------------------
property   value
1          John
2          Tim
3          Smith
4          Jones

Table: PropertiesDate
-------------------------
property   value
1          04-11-1966
2          07-08-1971

table: userPropertiesLinked
-----------------------------
user   property propertyValue
1      1        1
1      2        3
2      1        2
2      2        4
2      3        1
1      3        2

This, put together, shows us that we have 2 users, John Smith (04-11-1966) and Tim Jones (07-08-1971).

这个放在一起,向我们展示了我们有2个用户,John Smith(04-11-1966)和Tim Jones(07-08-1971)。

My question is: is this the best way to go at it? The way this DB is setup is very searchfriendly, people can have multiple properties, properties aren't stored twice, etc. The problem i'm running into, is the fact that "property" 1 in one table is John, and in the other 04-11-1966. Is there a way to make these values unique and to auto increment, even though they are in different tables? Is there a way to put them into one table, without risking double information or empty cells?

我的问题是:这是最好的方法吗?设置这个数据库的方式非常适合搜索,人们可以拥有多个属性,属性不会存储两次等等。我遇到的问题是,一个表中的“属性”1是John,并且其他04-11-1966。有没有办法让这些值独特并自动增加,即使它们在不同的表中?有没有办法将它们放入一个表中,而不会冒双重信息或空单元格的风险?

Any help would be much appreciated!

任何帮助将非常感激!

1 个解决方案

#1

I think you can consider the foreign key to connect with two tabls but the same values, like this:

我认为你可以考虑使用外键来连接两个tabl但是相同的值,如下所示:

constraint PropertiesString_fk foreign key (value) references  PropertiesDate(or other table) value.

As for auto increment, you can think like this:

至于自动增量,你可以这样思考:

ALTER TABLE tablename DROP id;
ALTER TABLE tablename ADD id INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

As how to put them into one table, you can use the command "select" and "distinct"

至于如何将它们放入一个表中,您可以使用命令“select”和“distinct”

#1